Copy the last cell data of each row from sheet1 and paste to sheet2

Copy the last cell data of each row from sheet1 and paste to sheet2
Avinash SinghAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Enter this formula in A1 and copy down


If your actual data is more than 10 columns then change both instances of J1 to K1 or L1 or even further.
Avinash SinghAuthor Commented:
I need to do this by vba only
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...

Sub copyLastCell()
Dim sws As Worksheet, dws As Worksheet
Dim lr As Long, i As Long, dlr As Long
Application.ScreenUpdating = False
Set sws = Sheets("Sheet1")
Set dws = Sheets("Sheet2")
lr = sws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lr
    If dws.Range("A1").Value = "" Then
        dlr = 1
        dlr = dws.Range("A" & Rows.Count).End(3)(2).Row
    End If
    dws.Range("A" & dlr).Value = sws.Cells(i, Columns.Count).End(xlToLeft).Value
Next i
Application.ScreenUpdating = True
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Avinash SinghAuthor Commented:
Thnx Neeraj Sir for giving ur precious time to this post and thnx for ur great support
Saqib Husain, SyedEngineerCommented:
You did not mention VBA.

You can do this without looping

Sub Macro1()
With Sheet2.Range(Sheet1.Range("A1:A" & Sheet1.Range("A1").End(xlDown).Row).Address)
     .FormulaR1C1 = "=INDEX(Sheet1!r,1,COUNT(Sheet1!r))"
     .Value = .Value
End With
End Sub
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Avinash!
Roy CoxGroup Finance ManagerCommented:
Here's how I would create a report using AdvancedFilter and some VBA to copy to a Template report
Saqib Husain, SyedEngineerCommented:
Wrong window?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.