How to copy last row to another sheet vba help.

[punnipah]

Hey friends.

Im New in VBA. Could you help me at this?



So, i have 2 columns: AB and 2 Rows : A1,A2,B1,B2 with data, in Sheet1
I want to COPY Last Row in AB Column from Sheet1
and paste it to Sheet2, column CD in FIRST EMPTY ROW when filter (i mean by adding more data, cause CD is supposed to have previous data)

Can anyone help? It would be a huge favour for me! THANKS !!

 

[Max1616]

Give this a try. I commented the code to let you know where you might need to make edits:

Sub copy_last_row()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lrow1 As Long
Dim lrow2 As Long
Dim Sheet1StartClm As Integer
Dim Sheet2StartClm As Integer

'These variables represent the sheet names
Set ws1 = Sheets("Sheet1") 'Rename "Sheet1" to the sheet you are copying data from
Set ws2 = Sheets("Sheet2") 'Rename "Sheet2" to the sheet you are copying data to

'These variables need to represent the FIRST column you are referenceing in each sheet
Sheet1StartClm = 1 '1 Represents Column A (Sheet1 column A - Where the data is being copied from)
Sheet2StartClm = 3 '3 Represents Column C (Sheet2 column C - Where the data is being pasted)

'These variables reference the last row in your target columns for each sheet
lrow1 = ws1.Cells(Rows.Count, Sheet1StartClm).End(xlUp).Row
lrow2 = ws2.Cells(Rows.Count, Sheet2StartClm).End(xlUp).Row

'This line of code copys the data in the last row of sheet 1, and pastes it into the last row of sheet 2 (for the target columns)
ws1.Range(Cells(lrow1, Sheet1StartClm), Cells(lrow1, Sheet1StartClm + 1)).Copy ws2.Cells(lrow2 + 1, Sheet2StartClm)

End Sub

Hopefully this helps!

 

[punnipah]

Give this a try. I commented the code to let you know where you might need to make edits:

Sub copy_last_row()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lrow1 As Long
Dim lrow2 As Long
Dim Sheet1StartClm As Integer
Dim Sheet2StartClm As Integer

'These variables represent the sheet names
Set ws1 = Sheets("Sheet1") 'Rename "Sheet1" to the sheet you are copying data from
Set ws2 = Sheets("Sheet2") 'Rename "Sheet2" to the sheet you are copying data to

'These variables need to represent the FIRST column you are referenceing in each sheet
Sheet1StartClm = 1 '1 Represents Column A (Sheet1 column A - Where the data is being copied from)
Sheet2StartClm = 3 '3 Represents Column C (Sheet2 column C - Where the data is being pasted)

'These variables reference the last row in your target columns for each sheet
lrow1 = ws1.Cells(Rows.Count, Sheet1StartClm).End(xlUp).Row
lrow2 = ws2.Cells(Rows.Count, Sheet2StartClm).End(xlUp).Row

'This line of code copys the data in the last row of sheet 1, and pastes it into the last row of sheet 2 (for the target columns)
ws1.Range(Cells(lrow1, Sheet1StartClm), Cells(lrow1, Sheet1StartClm + 1)).Copy ws2.Cells(lrow2 + 1, Sheet2StartClm)

End Sub

Hopefully this helps!

Thank you very much

 

[punnipah]

Give this a try. I commented the code to let you know where you might need to make edits:

Sub copy_last_row()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lrow1 As Long
Dim lrow2 As Long
Dim Sheet1StartClm As Integer
Dim Sheet2StartClm As Integer

'These variables represent the sheet names
Set ws1 = Sheets("Sheet1") 'Rename "Sheet1" to the sheet you are copying data from
Set ws2 = Sheets("Sheet2") 'Rename "Sheet2" to the sheet you are copying data to

'These variables need to represent the FIRST column you are referenceing in each sheet
Sheet1StartClm = 1 '1 Represents Column A (Sheet1 column A - Where the data is being copied from)
Sheet2StartClm = 3 '3 Represents Column C (Sheet2 column C - Where the data is being pasted)

'These variables reference the last row in your target columns for each sheet
lrow1 = ws1.Cells(Rows.Count, Sheet1StartClm).End(xlUp).Row
lrow2 = ws2.Cells(Rows.Count, Sheet2StartClm).End(xlUp).Row

'This line of code copys the data in the last row of sheet 1, and pastes it into the last row of sheet 2 (for the target columns)
ws1.Range(Cells(lrow1, Sheet1StartClm), Cells(lrow1, Sheet1StartClm + 1)).Copy ws2.Cells(lrow2 + 1, Sheet2StartClm)

End Sub

Hopefully this helps!

@Max1616


I have 1 more question if sheet 1 i need copy column A Only How try.

 

[Max1616]

If you only want to copy column A, then try this:

        Sub copy_last_row()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lrow1 As Long
Dim lrow2 As Long
Dim Sheet1StartClm As Integer
Dim Sheet2StartClm As Integer

'These variables represent the sheet names
Set ws1 = Sheets("Sheet1") 'Rename "Sheet1" to the sheet you are copying data from
Set ws2 = Sheets("Sheet2") 'Rename "Sheet2" to the sheet you are copying data to

'These variables need to represent the FIRST column you are referenceing in each sheet
Sheet1StartClm = 1 '1 Represents Column A (Sheet1 column A - Where the data is being copied from)
Sheet2StartClm = 3 '3 Represents Column C (Sheet2 column C - Where the data is being pasted)

'These variables reference the last row in your target columns for each sheet
lrow1 = ws1.Cells(Rows.Count, Sheet1StartClm).End(xlUp).Row
lrow2 = ws2.Cells(Rows.Count, Sheet2StartClm).End(xlUp).Row

'This line of code copys the data in the last row of sheet 1, and pastes it into the last row of sheet 2 (for the target columns)
ws1.Range(Cells(lrow1, Sheet1StartClm)).Copy ws2.Cells(lrow2 + 1, Sheet2StartClm)

End Sub