• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 43
  • Last Modified:

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
Book.xlsx
0
Avinash Singh
Asked:
Avinash Singh
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Enter this formula in A1 and copy down

=INDEX(Sheet1!A1:J1,1,COUNT(Sheet1!A1:J1))

If your actual data is more than 10 columns then change both instances of J1 to K1 or L1 or even further.
0
 
Avinash SinghAuthor Commented:
I need to do this by vba only
0
 
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")
dws.Columns(1).Clear
lr = sws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lr
    If dws.Range("A1").Value = "" Then
        dlr = 1
    Else
        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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Avinash SinghAuthor Commented:
Thnx Neeraj Sir for giving ur precious time to this post and thnx for ur great support
0
 
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
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Avinash!
0
 
Roy CoxGroup Finance ManagerCommented:
Here's how I would create a report using AdvancedFilter and some VBA to copy to a Template report
AdvancedFilter-REPORTING-.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
Wrong window?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now