Month: July 2021

In this article, you will see VBA to merge sheets in Excel.

You will see two flavors of VBA code

1st where the table in each sheet starts with the same cell

2nd where the table in each sheet starts with different cell

Merge sheets with consistent tables

You can use this VBA code for merging sheets in Excel if the below points are satisfied.

• Tables in each sheet start with the same cell address.
• Column order in each table is same.

In the below image on left side you can see three sheets. In each sheet, table is starting with cell A1 and the column order is also same.

At the right side of the image, you can see the table formed from merging of all three sheets. 

In the merged table you can see one extra column named “Sheet name” which contains sheet names.

Copy-paste the below VBA code section in the standard module.

 How to insert and run VBA code in Excel?

Sub MergeSheets()
  Dim masterSht As Worksheet
  Dim uptoShtIdx As Integer
  Dim sht As Worksheet, i As Integer
  Dim lRng As Range
  Dim cpyRng As Range, pstRng As Range
  Dim tblStrtRng As Range
  Set masterSht = Worksheets.Add
  uptoShtIdx = 3
  masterSht.Move after:=Worksheets(uptoShtIdx + 1)
  For i = 1 To uptoShtIdx
    Set sht = Worksheets(i)
    Set tblStrtRng = sht.Range("A1")
    Set lRng = tblStrtRng.SpecialCells(xlCellTypeLastCell)
    If i = 1 Then
        Set cpyRng = sht.Range(tblStrtRng, lRng)
        Set pstRng = masterSht.Range("B1")
        masterSht.Range("A1").Value = "Sheet name"
        masterSht.Range("A2").Resize(cpyRng.Rows.Count - 1, 1).Value = sht.Name
        pstRng.Resize(cpyRng.Rows.Count, cpyRng.Columns.Count).Value = cpyRng.Value
    Else
        Set cpyRng = sht.Range(tblStrtRng.Offset(1), lRng)
        Set pstRng = masterSht.Range("A" & Rows.Count).End(xlUp).Offset(1, 1)
        pstRng.Offset(, -1).Resize(cpyRng.Rows.Count, 1).Value = sht.Name
        pstRng.Resize(cpyRng.Rows.Count, cpyRng.Columns.Count).Value = cpyRng.Value
    End If
  Next i
  masterSht.Range("A1").CurrentRegion.EntireColumn.AutoFit
  MsgBox "Sheets got merged"
  masterSht.Activate
End Sub

Variable uptoShtIdx represents how many sheets from the left side you want merge.

In this case I kept to 3 uptoShtIdx = 3

In the below image I want to merge Jan, Feb, Mar sheets (3 sheets from the left side). You can change this as per your requirement.

Note:- For this VBA sheets to be merged must be arranged continuously from left to right.  

Variable tblStrtRng represents at which range tables in each sheet starts. In this case it is Set tblStrtRng = sht.Range(“A1”). 

You can change “A1” to some other range.

Merge sheets with inconsistent tables

In this case table in each sheet is not consistent, each table starts with a different cell.

In the below image you can see the table of Jan, Feb, Mar sheets start with different cells.

Paste the below VBA code section for merging sheets in the standard module.

Sub MergeSheets_2()
  Dim masterSht As Worksheet
  Dim uptoShtIdx As Integer
  Dim sht As Worksheet, i As Integer
  Dim cpyRng As Range, pstRng As Range
  Dim tblStrtRng As Range
  Dim headNm As String
  Set masterSht = Worksheets.Add
  uptoShtIdx = 3
  masterSht.Move after:=Worksheets(uptoShtIdx + 1)
  headNm = "Category" ''any column heading name of a table
  For i = 1 To uptoShtIdx
    Set sht = Worksheets(i)
    Set tblStrtRng = sht.Range(sht.Range("A1"), sht.Range("A1") _
    .SpecialCells(xlCellTypeLastCell))
    Set tblStrtRng = tblStrtRng.Find _
    (what:=headNm, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If i = 1 Then
        Set cpyRng = tblStrtRng.CurrentRegion
        Set pstRng = masterSht.Range("B1")
        masterSht.Range("A1").Value = "Sheet name"
        masterSht.Range("A2").Resize(cpyRng.Rows.Count - 1, 1).Value = sht.Name
        pstRng.Resize(cpyRng.Rows.Count, cpyRng.Columns.Count).Value = cpyRng.Value
    Else
        Set cpyRng = tblStrtRng.CurrentRegion.Offset(1)
        Set pstRng = masterSht.Range("A" & Rows.Count).End(xlUp).Offset(1, 1)
        pstRng.Offset(, -1).Resize(cpyRng.Rows.Count - 1, 1).Value = sht.Name
        pstRng.Resize(cpyRng.Rows.Count - 1, cpyRng.Columns.Count).Value = cpyRng.Value
    End If
  Next i
  masterSht.Range("A1").CurrentRegion.EntireColumn.AutoFit
  MsgBox "Sheets got merged"
  masterSht.Activate
End Sub

Variable headNm represents one of the heading names of the table. 

headNm = “Category” at the place of Category you can write some other heading name, but make sure this heading is there in all the tables which is to be merged.

Download related Excel files

Merge sheets into one (Consistent table)

Merge sheets into one (Inconsistent table)