Combine multiple Excel workbooks into sheets in a single workbook
It may be useful to have multiple source workbooks which can be individually updated and replaced, then combine them into a single combined workbook for actual use. In my case, the combined workbook is used as a data source in Tableau, with each sheet acting as a table.
Below is a VBA subroutine which can be run in Excel (in the workbook which will be the combined file). The subroutine assumes that all the new, updated files are in a
source/ directory which is in the combined file's directory.
Importantly, this subroutine only copies visible sheets (ignoring hidden and very hidden sheets).
Caution: For this script to work, the sheet name in the source files _must_ match the corresponding sheet name in the combined file.
Sub UpdateSheetsFromSourceFiles() 'Disable alerts to delete silently Application.DisplayAlerts=FALSE 'We assume source files are in the source\ directory path = ActiveWorkbook.Path & "\source\" filename = Dir(path & "*.xlsx") Do While filename <> "" Workbooks.Open Filename:=path & filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets If Sheet.Visible = -1 Then 'Only if sheet is visible 'Remove old version of sheet to update, then pull in the updated version ThisWorkbook.Sheets(Sheet.Name).Delete Sheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Worksheets.Count) End If Next Sheet Workbooks(filename).Close filename = Dir() Loop 'Re-enable alerts Application.DisplayAlerts=TRUE End Sub
How it works
- First, we disable alerts. This allows us to delete files without asking for confimation from the user, making it nice and silent.
The path is determined from the
ActiveWorkbook, which in this case is the combined file where the subroutine is running. Using the
Dirfunction, we get the first
xlsxfile in the
Looping through each sheet in the workbook, we first check to see if the sheet is visible (
Visible = -1). If it is, we get to the real workhorse of the function:
In the combined file (
ThisWorkbook), the matching sheet is deleted, then:
- The sheet from the source file is copied in. It's placed at the end of the list of sheets. Since we're looping through the directory, this has the side-effect of alphabetizing the worksheets.
- In the combined file (
Once all the sheets in a workbook have been looped through, we close it and move on to the next file which matches the string we gave at the start (calling
Dirwith no arguments returns the next matching file).