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.

Unlock Your Potential

Excel

Basic - Advanced

Access

Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project