copy all 4 sheets data and paste to sheet5

I want to copy data from sheet1,sheet2,sheet3,sheet4 and paste the data to sheet5
copy all 4 sheets data and paste to sheet5
kindly see sheet5
this highlighted colour is only for understanding purpose
Book1.xlsm
Avinash SinghAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
You may try something like this. I am assuming the order of Names is same in all the worksheets.
Sub CopyData()
Dim ShArr, Sh
Dim dws As Worksheet, ws As Worksheet
Dim lr As Long, lc As Long, i As Long, dlc As Long

Application.ScreenUpdating = False

Set dws = Sheets("Sheet5")
ShArr = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
dws.Cells.Clear
For Each Sh In ShArr
    Set ws = Sheets(Sh)
    lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
    If dws.Range("A1").Value = "" Then ws.Range("A2:A" & lr).Copy dws.Range("A1")
    For i = 2 To lr
        lc = ws.Cells(i, Columns.Count).End(xlToLeft).Column
        If lc > 1 Then
            dlc = dws.Cells(i - 1, Columns.Count).End(xlToLeft).Column + 1
            ws.Range("B" & i, ws.Cells(i, lc)).Copy dws.Cells(i - 1, dlc)
        End If
    Next i
Next Sh
Application.ScreenUpdating = True
End Sub

Open in new window


In the attached, you can click the button called "Get All Data" on Sheet5 to run the code.
CopyData.xlsm
0
 
Avinash SinghAuthor Commented:
Thnx Neeraj sir for giving ur precious time to this post
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Avinash!
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.

All Courses

From novice to tech pro — start learning today.