Combine Active Workbook All Sheets Into One Master Sheet With VBA

We always wish to have a solution with one click that can combine data from multiple sheets into one master sheet. This simple VBA Code allows you to combine all the sheets into one single sheet.

You can use the code in Excel for mac & windows

1. Copy the below VBA Code by clicking on the copy box
2. Open Microsoft Visual Basic for Applications window Developer Ribbon Tab > Visual Basic (Shortcut keys as follows for mac & windows)
   1. mac users fn  + ⌥ [option] + F11 
   2. windows users Alt  + F11
3. From the menu bar select Insert > Module and insert new Modules
4. Paste the code into the module.

Note: It is always to take a back-up copy of your original before running the code on the original file.

Sub AppendAllSheetsData()
  'Made it easy by ExcelExciting.com
    Dim i As Integer
    On Error Resume Next
    'Create New Sheet called as MergedSheet
    Sheets(1).Select
    Worksheets.Add
    'You can rename the sheet name here
    Sheets(1).Name = "MergedSheet"
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")
    'Loop Start Here to combine the sheets
    For i = 2 To Sheets.Count
        Sheets(i).Activate
        Range("A1").Select
        Selection.CurrentRegion.Select
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next i
    Sheets(1).Activate
End Sub

If you like this post make sure you share it 🙂

Video

Subscribe to YouTube Channel for Exciting Tip & Trick On Office Applications

Was the information helpful in this blog?

• If yes, hit that share button and show the excitement to the world.
• Subscribe to our free posts.

If no, please let us know what to improve.

Having trouble with any Office Apps. Feel free to ask and answer queries at our forums section.