copy data from multiple sheets into a master sheet in excel vba

Question

i need to add all the cells' contents after cell (A14) for sheet2 till sheet13 within the same workbook (the range of data is unknown it depends on external source) and the name of each sheet should appear before the data in the summary sheet as title.

Any help would be much appreciated.

Sub Copy_Sheets_To_Master()
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
Dim Lastrow As Long
Dim Lastrowa As Long
Dim Lastrowd As Long

    For i = 2 To Sheets.Count

        ans = Sheets(i).Name
        Lastrowa = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
        Sheets(i).Range("A14" & Lastrowa).Copy Sheets("Master").Range("A" & Lastrow)
        Lastrowd = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
        Sheets("Master").Range("D" & Lastrow & ":D" & Lastrowd).Value = ans
        Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1

    Next
Application.ScreenUpdating = True
End Sub

cheers,

If you try "How to copy data from one sheet to another" in the search box you get at least 8 pages of results in SO. If you enter that in Google you get about a-hundred-and-eleventy-million results. Could you show us what you've tried please and worth having a read of these links - tour, How to Ask, minimal reproducible example — Darren Bartrup-Cook
Thanks for the comment and you are right, but my problem is that i can't specify the range of data and add the title before them. — Oday

Unknown Unknown · Accepted Answer · 2018-07-02T13:22:52

Try with something like this:

Sub Copy_Sheets_To_Master()

Dim wb As Workbook
Dim ws As Worksheet
Dim i, LastRowa, LastRowd As Long
Dim WSname As String

Set wb = ActiveWorkbook

For Each ws In wb.Sheets

If ws.Name <> "Master" Then

WSname = ws.Name

LastRowa = ws.Cells(Rows.Count, "A").End(xlUp).Row
LastRowd = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row

ws.Range("A14:A" & LastRowa).Copy
Sheets("Master").Range("A" & LastRowd + 2).PasteSpecial Paste:=xlPasteValues
Sheets("Master").Range("A" & LastRowd + 1).Value = WSname

End If

Next ws

End Sub

copy data from multiple sheets into a master sheet in excel vba

1 Answers