98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » How to copy cells from last row in one worksheet to cells in ano
How to copy cells from last row in one worksheet to cells in ano
Resolved · Low Priority · Version 2019
Sun 14 Mar 2021, 15:28Delegate steven said...
steven has attended:
Office 365 End User course
Excel VBA Intermediate 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
Sun 14 Mar 2021, 16:00Trainer Jens said...
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
Mon 15 Mar 2021, 09:27Delegate steven said...
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
Mon 15 Mar 2021, 09:33Delegate steven said...
RE: How to copy cells from last row in one worksheet to cells in
Apologies, got this working now.
Thanks!
Mon 15 Mar 2021, 09:39Delegate steven said...
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.
Mon 15 Mar 2021, 09:52Delegate steven said...
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...
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Make a quick copy of a worksheetHold down the Ctrl key, then click and drag on a sheet tab to make a copy of that sheet. Though this process usefully copies the formats of the original sheet, note that any Range Names you have on the original sheet will be duplicated too. |
