Macro for getting the right data from Last Row

[skarnik01]

Hi,

I am having a macro, wherein on clicking the Add button, it identifies the last row and then adds a Record after the last row.
I require to add a feature over here, wherein on clicking the Add button, before the new row gets added, the macro should be able to copy the values present in columns - A, C, D & E of the last row and paste it into another sheet [cells K3 to N3].
(Please note that all cells in the mentioned columns have values present, i.e. there are no blank cells in these columns)
Thanks in advance

 

[Luke M]

Assuming you already know what the number of last row is, but you could do something like this.

Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Copies data from last row, cols A,C,D,E to Sheet2
Intersect(Cells(lastRow, 1).EntireRow, Range("A:A,C:E")).Copy Worksheets("Sheet2").Range("K3")

 

[skarnik01]

Thanks Luke. It works.

I was trying to further add these lines into With statement for the sheet, however, the Intersect command didn't function.

 

[Luke M]

Example using a With statement:

Dim lastRow As Long
With Worksheets("Some Sheet")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

'Copies data from last row, cols A,C,D,E to Sheet2
Intersect(.Cells(lastRow, 1).EntireRow, .Range("A:A,C:E")).Copy Worksheets("Sheet2").Range("K3")
End With

Intersect itself is a Method, with no parent object, so it does not need a leading period.