Copy & Paste from select range to another sheet

[Halim]

Dear VBA Expert,

Iam needing your help to resolve my VBA error.

I am trying to record (copy) the following data entry (daily change)

On sheet 1 ; Cell C11, G14, G19, C37 and G10

Paste onto next (Continuous data buildup)

On sheet 2 : Cell D3, B3, G3, E3 and F3 & Last Row

I am using the following VBA, however it does not copying to last row, appreciate your help.

Private Sub CommandButton1_Click()
Dim Last_Row As Long
Sheet1.Range("C11").Copy Sheet2.Range("D3").End(xlUp).Row + 1
Sheet1.Range("G14").Copy Sheet2.Range("B3").End(xlUp).Row + 1
Sheet1.Range("G19").Copy Sheet2.Range("G3").End(xlUp).Row + 1
Sheet1.Range("C37").Copy Sheet2.Range("E3").End(xlUp).Row + 1
Sheet1.Range("G10").Copy Sheet2.Range("F3").End(xlUp).Row + 1
End Sub

Rgds, Halim

 

[My Aswer Is This]

Try this:

Sub Copy_Me()
'Modified  4/21/2019  6:40:28 AM  EDT
Sheet1.Range("C11").Copy Sheet2.Cells(Cells(Rows.Count, "D").End(xlUp).Row + 1, "D")
Sheet1.Range("G14").Copy Sheet2.Cells(Cells(Rows.Count, "B").End(xlUp).Row + 1, "B")
Sheet1.Range("G19").Copy Sheet2.Cells(Cells(Rows.Count, "G").End(xlUp).Row + 1, "G")
Sheet1.Range("C37").Copy Sheet2.Cells(Cells(Rows.Count, "E").End(xlUp).Row + 1, "E")
Sheet1.Range("G10").Copy Sheet2.Cells(Cells(Rows.Count, "F").End(xlUp).Row + 1, "F")
End Sub

 

[PCL]

See next code

Private Sub CommandButton1_Click()
Dim Last_Row As Long
Sheet1.Range("C11").Copy Sheet2.Range("D3").End(xlUp)(2)
Sheet1.Range("G14").Copy Sheet2.Range("B3").End(xlUp)(2)
Sheet1.Range("G19").Copy Sheet2.Range("G3").End(xlUp)(2)
Sheet1.Range("C37").Copy Sheet2.Range("E3").End(xlUp)(2)
Sheet1.Range("G10").Copy Sheet2.Range("F3").End(xlUp)(2)
End Sub

 

[MARK858]

@PCL, that will find the last used cell upwards from row 3 offset by one row, not the last used cell.

To test put something in Sheet1 C11 and something in Sheet2 D7 then run the code below and see where it places the data.

Sub xxx()
Sheet1.Range("C11").Copy Sheet2.Range("D3").End(xlUp)(2)
End Sub

It needs the Rows.Count as used by My Aswer Is This although not as many "Cells" as

Sheet1.Range("C11").Copy Sheet2.Range("D" & Rows.count).End(xlUp)(2)

or

Sheet1.Range("C11").Copy Sheet2.Cells(Rows.count, "D").End(xlUp)(2)

will suffice