How to merge multiple sheets into one sheet using VBA?

To merge multiple sheets into one sheet using VBA in Excel, you can create a macro that loops through each sheet in the workbook, copies their contents, and then pastes them into a new or existing summary sheet. Here’s a step-by-step guide with an example VBA code:

   Sub MergeSheets()
       Dim ws As Worksheet
       Dim wsSummary As Worksheet
       Dim wsName As String
       Dim lastRow As Long
       Dim targetCell As Range
       
       ' Name of the summary sheet
       wsName = "Summary"
       
       ' Check if the summary sheet already exists, if not create it
       On Error Resume Next
       Set wsSummary = ThisWorkbook.Worksheets(wsName)
       If wsSummary Is Nothing Then
           Set wsSummary = ThisWorkbook.Worksheets.Add
           wsSummary.Name = wsName
       Else
           ' If it exists, clear previous content
           wsSummary.Cells.Clear
       End If
       On Error GoTo 0

       ' Set the starting point for data in the summary sheet
       Set targetCell = wsSummary.Cells(1, 1)
       
       ' Loop through each sheet in the workbook
       For Each ws In ThisWorkbook.Worksheets
           ' Avoid copying from the summary sheet itself
           If ws.Name <> wsName Then
               ' Find the last row in the current sheet
               lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
               
               ' Copy data from current sheet to the summary sheet
               ws.Range("A1", ws.Cells(lastRow, ws.Columns.Count)).Copy
               
               ' Paste into the summary sheet at the current target cell position
               targetCell.PasteSpecial Paste:=xlPasteValues
               
               ' Update targetCell to the next available row in summary sheet
               Set targetCell = wsSummary.Cells(wsSummary.Cells(wsSummary.Rows.Count, "A").End(xlUp).Row + 1, 1)
           End If
       Next ws

       ' Clear clipboard to release memory
       Application.CutCopyMode = False

       MsgBox "Sheets successfully merged into " & wsName
   End Sub

Open your Excel workbook where you want to merge sheets.
Press `ALT` + `F11` to open the Visual Basic for Applications (VBA) editor.
Insert a new module:

In the VBA editor, click on `Insert` in the menu.
Select `Module` to add a new module to your workbook.

Copy and paste the following code into the module window:
Run the macro:

Close the VBA editor and return to Excel.
Press `ALT` + `F8`, select `MergeSheets`, and click `Run`.

Explanation:

The macro will create a summary sheet named “Summary”.
It loops over all the sheets in the workbook.
It copies the contents from each sheet (excluding the summary sheet itself) and pastes them sequentially into the summary sheet.
The new data is pasted starting at the last available row to avoid overwriting any existing data.
The macro assumes data starts in column A of each sheet and extends to the rightmost used column automatically.

Notes:

Modify `ws.Range(“A1”, ws.Cells(lastRow, ws.Columns.Count))` if your data starts at a different cell.
This code assumes that there are no header rows to handle. If your data includes headers, you might want to adjust the code to skip headers after the first copied sheet.

facebooklinkedin