Collection of worksheets


Sometimes we need to combine sheets from different Excel workbooks to create a new report or consolidated data file. Such an operation can be done manually, using standard MS Excel tools, but it can take a lot of time and effort. Let's consider how we can automate this process and speed up work with reports.

For example, we have many workbooks Excel, and we want to collect worksheets from all workbooks into one file. To do this, open the workbook where you want to collect all the sheets and add the following macro code.

VBA code for the macro

In the open workbook Excel where you want to collect all sheets, open Visual Basic Editor (Alt+F11), add the VBA module (Insert - Module) and copy this code macro there:

Sub CombineWorkbooks()
    Dim FilesToOpen
    Dim x As Integer
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    FilesToOpen = Application.GetOpenFilename _
                  (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
                   MultiSelect:=True, Title:="Files to Merge")
    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No file was selected!"
        GoTo ExitHandler
    End If
    x = 1
    While x <= UBound(FilesToOpen)
        Workbooks.Open Filename:=FilesToOpen(x)
        Sheets().Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        x = x + 1
    Application.ScreenUpdating = True
    Exit Sub
    MsgBox Err.Description
    Resume ExitHandler
End Sub

After that, you can return to Excel and run our macro through the menu Tools - Macro - Macros (Alt+F8). A dialog box will appear, where you need to select one or more (holding Ctrl) files, the sheets of which we want to add to the current workbook.

If you use a different Excel file extension (for example, *.xlsx, *.xlsm, *.xlsb, etc.), then you will need to make the appropriate changes to the ninth line of code in our macro.

Related Articles:

  • Sorting worksheets in a book
  • Quick switch between worksheets