copy and paste macro help.

[steve400243]

Hello, I use this macro to copy and Paste from WS1 to WS2. But I need it to copy and paste new data on the next row down on Ws2 When the button is selected. I cant figure this out. Thanks for all help provided.

Sub ButtonCode()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim DestRow As Long
    Set ws1 = Sheets("Cartage Rate")
    Set ws2 = Sheets("Complete")
    DestRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    ws1.Range("B3").Copy ws2.Range("B" & DestRow)
    ws1.Range("C3").Copy ws2.Range("C" & DestRow)
    ws1.Range("D3").Copy ws2.Range("D" & DestRow)
    ws1.Range("E3").Copy ws2.Range("E" & DestRow)
    ws1.Range("F3").Copy ws2.Range("F" & DestRow)
    ws1.Range("D19").Copy ws2.Range("G" & DestRow)
    ws1.Range("E19").Copy ws2.Range("H" & DestRow)
    ws1.Range("F19").Copy ws2.Range("I" & DestRow)
End Sub

 

[JLGWhiz]

Change this:

DestRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1

To this

DestRow = ws2.Cells(Rows.Count, "B").End(xlUp).Row + 1

 

[steve400243]

Well that was easy - Thank you For your time in looking at it.

 

[My Aswer Is This]

Your not copying anything into column A on sheet2 but your using column A looking for data in lastrow

Why are you doing that?

 

[BAlGaInTl]

Change this:

DestRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1

To this

DestRow = ws2.Cells(Rows.Count, "B").End(xlUp).Row + 1

I looked at this 3 times and couldn't figure out why it wasn't doing it. Didn't realize the OP wasn't putting anything in "A".

 

[steve400243]

Column A on Sheet 2 is a Header Row.

 

[turbo805]

Edit. Nvm, others already offered a better solution

 

[steve400243]

Sorry read that wrong - Column A is where I will Put the Date In.

 

[My Aswer Is This]

You could write your script like this:

Sub ButtonCode()
    'Modified  11/27/2018  8:26:42 PM  EST
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim DestRow As Long
    Set ws1 = Sheets("Cartage Rate")
    Set ws2 = Sheets("Complete")
    DestRow = ws2.Cells(Rows.Count, "B").End(xlUp).Row + 1
    ws1.Range("B3:F3").Copy ws2.Range("B" & DestRow)
    ws1.Range("D19:F19").Copy ws2.Range("G" & DestRow)
    End Sub

 

[steve400243]

You could write your script like this:

Sub ButtonCode()
    'Modified  11/27/2018  8:26:42 PM  EST
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim DestRow As Long
    Set ws1 = Sheets("Cartage Rate")
    Set ws2 = Sheets("Complete")
    DestRow = ws2.Cells(Rows.Count, "B").End(xlUp).Row + 1
    ws1.Range("B3:F3").Copy ws2.Range("B" & DestRow)
    ws1.Range("D19:F19").Copy ws2.Range("G" & DestRow)
    End Sub

Thanks My Answer - This works great as well. I appreciate your time in looking at it. F19 Is a sum total, and when it copies over I loose the amount? Any ideas?

=SUM(F13+F15+F17-E19)