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 Dir function, we get the first xlsx file in the source\ directory.
• 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.
• 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 Dir with no arguments returns the next matching file).