Macros - Merge Multiple Workbooks with common worksheet names

Poppaby

New Member
Joined
Nov 19, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
Hi all

I have a folder with 20 workbooks. Each workbook has roughly 14 tabs. The tab naming is consistent across all workbooks, however some workbooks might not have all tabs (some have 12, 10,14 etc)

I would like a macros to merge all the workbooks into a single workbook, combining all the tabs with similar names into one tab with that name in the workbook, with the column headers being used once if possible.

Any help you can provide would be greatly appreciated

Dave
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,624
Office Version
  1. 2016
Platform
  1. Windows
Helpers need to know what happened to content of each tab of similar name when they are merged together. How data in each tab looks like if they are to be cascaded or added.
 

Poppaby

New Member
Joined
Nov 19, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
Thank you for coming back to me so quickly

example:

for each workbook with a tab called "workers" I would like all those tabs merged into a single tab called "workers" with the contents of all those tabs combined in the tab "workers". SO if there are 20 rows in one "workers" tab, 12 in another and 15 in another, the final tab will have 47 rows.
 

Poppaby

New Member
Joined
Nov 19, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
As if I was cut/paste all info across multiple tabs of the same name into a single tab to create a database.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,624
Office Version
  1. 2016
Platform
  1. Windows
This is a simplified code. The Target workbook (where to code reside) need to be constructed with all the sheet names that might exist in all the other workbooks that you are going to extract data from (I refer as source workbooks). The source workbooks do not have to have all the sheets existed in the Target workbook though.
VBA Code:
Sub ConsolidateData()

Dim LastRowTarget As Long
Dim strExt As String, strTab As String, ArryTab() As String
Dim LastSourceCell As Range
Dim TabName As Variant
Dim wsSource As Worksheet, wsTarget As Worksheet
Dim wbSource As Workbook, wbTarget As Workbook

' Name all Tabs existed in workbook Target here separated by space
strTab = "Sheet1 Sheet2 Sheet3"

ArryTab = Split(strTab)

' Set your folder path nad file extension here
Const strPath As String = "C:\Test\"
strExt = Dir("*.xlsx")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wbTarget = ActiveWorkbook

On Error Resume Next
ChDir strPath
    
Do While strExt <> ""
    Set wbSource = Workbooks.Open(strPath & strExt)
    For Each TabName In ArryTab
        If SheetExists(wbSource, TabName) Then
            Set wsTarget = wbTarget.Sheets(TabName)
            Set wsSource = wbSource.Sheets(TabName)
            If WorksheetFunction.CountA(wsTarget.Cells) = 0 Then
                LastRowTarget = 1
            Else
                LastRowTarget = wsTarget.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
            End If
            Set LastSourceCell = wsSource.Cells.SpecialCells(xlCellTypeLastCell)
        Else
            Exit For
        End If
        wsSource.Range("A1", LastSourceCell).Copy wsTarget.Range("A" & LastRowTarget)
        wbSource.Close False
    Next
    strExt = Dir
Loop

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
On Error GoTo 0
End Sub

Function SheetExists(wb As Workbook, FindSheet As Variant) As Boolean

SheetExists = False
For Each Sheet In wb.Worksheets
    If FindSheet = Sheet.Name Then
        SheetExists = True
        Exit Function
    End If
Next Sheet

End Function
 

Forum statistics

Threads
1,141,817
Messages
5,708,760
Members
421,588
Latest member
Wawie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top