CODEWITHSUNDEEP
excelvba
Larryc
Larryc

Reputation: 13

how to send data from a calculator table into the next row on another sheet in excel

I am wondering if anyone can help me out with writing a vba that will enable me to copy and paste the information input into a calculator into specifically mapped fields on a second worksheet. I would need the copy and paste to move down to the next available row each time as I do not want to overwrite the information.

I have a table that runs some calculations based on a quote value on a worksheet called "one". I need to figure out how to then copy and paste the values input in this calculator into a worksheet called "two".

The table starts at cell C16 with the additional information being spread across a few different cells.

Enquirer Name = F10 Enquiry Date = F12

Quote Value = C20

Percentage retained = C22

Processing fee = C23

Misc cost = C24

percentage 2 = c25

Cost = C26

Earning Value = C27

Per Annum Information

Value cell = D20

Percentage retained = D22

Processing fee = D23

Misc cost = D24

percentage 2 = D25

Cost = D26

Earning Value = D27

Per month Information

Value cell = E20

Percentage retained = E22

Processing fee = E23

Misc cost = E24

percentage 2 = E25

Cost = D26

Earning Value = E27

Per Week

Value cell = F20

Percentage retained = F22

Processing fee = F23

Misc cost = F24

percentage 2 = F25

Cost = F26

Earning Value = F27

Per Day

Value cell = G20

Percentage retained = G22

Processing fee = G23

Misc cost = G24

percentage 2 = G25

Cost = G26

Earning Value = G27

The Per Annum, Per Month, Per Week, Per Day fields are all auto populated based on formulas dividing the Quote value cell C20 by 12/4/52/365.

The question I have is, does anyone know a way that I can apply a macro button so that when this calculator is complete I can press the button to copy and paste the information into a table on sheet "two" and delete the original sheet "one" calculator information.

The destination sheet starts at A1 on sheet "two"

The destination cells from Sheet "one" to sheet "two" are:

f12 -> A2

f10 -> B2

C20 -> D2

C22 -> E2

C23 -> F2

C24 -> G2

C25 -> K2

C26 -> I2

C27 -> L2

If anyone can provide any assistance with this it would be majorly appreciated!

This is the code I have so far.

Option Explicit

Sub copyRow()
Dim ws As Worksheet
Dim lRow As Long

Set ws = ActiveWorkbook.Sheets("one")

lRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1

ws.Range("F12").Copy ws.Range("A2" & lRow)
ws.Range("F10").Copy ws.Range("B2" & lRow)
ws.Range("C20").Copy ws.Range("D2" & lRow)
ws.Range("C22").Copy ws.Range("E2" & lRow)
ws.Range("C23").Copy ws.Range("F2" & lRow)
ws.Range("C24").Copy ws.Range("G2" & lRow)
ws.Range("C25").Copy ws.Range("K2" & lRow)
ws.Range("C26").Copy ws.Range("I2" & lRow)
ws.Range("C27").Copy ws.Range("L2" & lRow)
ws.[A1].Select


End Sub

Thanks!.

Upvotes: 0

Views: 105

Answers (1)

DisplayName
DisplayName

Reputation: 13386

Though Range("A2" & lRow) could be a correct syntax, I'd guess you'd need Range("A" & lRow)

Moreover, your question wording isn't consistent with your shown code

if you want to da as per your wording:

Option Explicit

Sub copyRow()
    Dim ws1 As Worksheet, ws2 As Worksheet

    Set ws1 = ActiveWorkbook.Sheets("one") ' set "source" sheet
    Set ws2 = ActiveWorkbook.Sheets("two") ' set "destination" sheet

    ws2.Range("A2").Value = ws1.Range("F12").Value
    ws2.Range("B2").Value = ws1.Range("F10").Value
    ws2.Range("D2").Value = ws1.Range("C20").Value
    ws2.Range("E2:G2").Value = ws1.Range("C22:C24").Value
    ws2.Range("K2").Value = ws1.Range("C25").Value
    ws2.Range("I2").Value = ws1.Range("C26").Value
    ws2.Range("L2").Value = ws1.Range("C27").Value

    ws1.Range("F10,F12, C20, C22:C27").ClearContents ' clear contents of "source" cells worksheet "one"
End Sub

whle if you want to do as per your shown code "intention":

Option Explicit

Sub copyRow2()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lRow As Long

    Set ws1 = ActiveWorkbook.Sheets("one") ' set "source" sheet
    Set ws2 = ActiveWorkbook.Sheets("two") ' set "destination" sheet

    lRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1 ' get destination sheet column A last not empty cell row index

    ws2.Range("A" & lRow).Value = ws1.Range("F12").Value
    ws2.Range("B" & lRow).Value = ws1.Range("F10").Value
    ws2.Range("D" & lRow).Value = ws1.Range("C20").Value
    ws2.Range("E" & lRow & ":G" & lRow).Value = ws1.Range("C22:C24").Value
    ws2.Range("K" & lRow).Value = ws1.Range("C25").Value
    ws2.Range("I" & lRow).Value = ws1.Range("C26").Value
    ws2.Range("L" & lRow).Value = ws1.Range("C27").Value

    ws1.Range("F10,F12, C20, C22:C27").ClearContents ' clear contents of "source" cells worksheet "one"
End Sub

Upvotes: 0

Related Questions