Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

how copy cells last

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

ResolvedVersion 2019

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...

RE: How to copy cells from last row in one worksheet to cells in

This works:

sourceWs.Range("A1").End(xlDown).Copy Destination:=dstWs.Range("A3")

 

Training courses

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Closing Multiple Workbooks quickly

When you have several workbooks open in Excel and want to just close them all at once:

1) Hold down the SHIFT key before selecting the File menu.

2) Once in File menu release SHIFT key and select Close All option.

3) All your files will close. If files require saving Excel will ask if you want to save the changes.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.