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 make collection of worksheets from all books into one file. To do this, open the workbook where you want to collect all the sheets and add the following macro code.
With the Excel workbook open, where you want to collect all the sheets, open the Visual Basic Editor (Alt+F11), insert module VBA ( Insert - Module ) and copy this macro code there:
Sub CombineWorkbooks()
'moonexcel.com.ua
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
Wend
ExitHandler:
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
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 our macro's code.