steven has attended:
Excel VBA Intermediate course
Excel VBA Intermediate course
Excel VBA Intermediate course
Office 365 End User course
How to copy cells from last row in one worksheet to cells in ano
Hi,
I am trying to find a way to locate the last row in a table and copy certain cells from the last row to cells in another worksheet.
Any ideas please?!
Thanks
Steve
RE: How to copy cells from last row in one worksheet to cells in
Hi Steven,
Thank you for the forum question.
The two lines below can help you.
The first line below find the last row in a table on sheet1 and move the value of the last row Column A to A1 on Sheet2. If your source table do not start from A1, you will need to change "Range("a1").CurrentRegion.Rows.Count". If your source table starts from B10 it should be changed to "Range("B10").CurrentRegion.Rows.Count"
The second line below find the last row in a table on sheet1 and move the value of the last row Column C to A2 on Sheet2.
Sheets("Sheet2").Range("a1") = Sheets("Sheet1").Rows(Range("a1").CurrentRegion.Rows.Count).Range("a1")
Sheets("Sheet2").Range("a2") = Sheets("Sheet1").Rows(Range("a1").CurrentRegion.Rows.Count).Range("c1")
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: How to copy cells from last row in one worksheet to cells in
Thanks Jens,
I think this copies the whole row across, is there a way to choose the cells you wish to copy over from the last row found?
Thanks
Steve
RE: How to copy cells from last row in one worksheet to cells in
Apologies, got this working now.
Thanks!
RE: How to copy cells from last row in one worksheet to cells in
Actually Jens,
Trying it again it does not find the last row, it only takes data from the specified row.
RE: How to copy cells from last row in one worksheet to cells in
Sub CopyProgress()
Dim sourceWs As Worksheet, dstWs As Worksheet
Set sourceWs = Sheets("Progress")
Set dstWs = Sheets("Test")
dstWs.Range("a1") = sourceWs.Rows(Range("a1").CurrentRegion.Rows.Count).Range("b2")
dstWs.Range("b1") = sourceWs.Rows(Range("a1").CurrentRegion.Rows.Count).Range("c2")
dstWs.Range("c1") = sourceWs.Rows(Range("a1").CurrentRegion.Rows.Count).Range("d2")
dstWs.Range("d1") = sourceWs.Rows(Range("a1").CurrentRegion.Rows.Count).Range("e2")
dstWs.Range("e1") = sourceWs.Rows(Range("a1").CurrentRegion.Rows.Count).Range("f2")
dstWs.Range("f1") = sourceWs.Rows(Range("a1").CurrentRegion.Rows.Count).Range("g2")
End Sub
This takes the first row of data, but doesn't find the last row of data and copy from there...
