Let’s say you have a bunch of Excel workbooks, all of which need to be merged into one file. Copying by hand is long and painful, so it makes sense to use a simple macro.
We open the book where we want to collect sheets from other files, we enter the Visual Basic editor with a keyboard shortcut Alt+F11 button of the same name on the tab developer (Developer — Visual Basic), add a new empty module (in the menu Insert — Module) and copy the text of this macro there:
Sub CombineWorkbooks() Dim FilesToOpen Dim x As Integer Application.ScreenUpdating = False 'disable screen updating for speed 'call dialog for selecting files to import FilesToOpen = Application.GetOpenFilename _ (FileFilter:="All files (*.*), *.* ", _ MultiSelect:=True, Title:="Files to Merge") If TypeName(FilesToOpen) = "Boolean" Then MsgBox "No files selected!" Exit Sub End If 'go through all selected files x = 1 While x <= UBound(FilesToOpen) Set importWB = Workbooks.Open(Filename:=FilesToOpen(x)) Sheets().Copy After:=ThisWorkbook.Sheets(ThisWorkbook. Sheets.Count) importWB.Close savechanges:=False x = x + 1 Wend Application.ScreenUpdating = True End Sub
After that, you can return to Excel and run the created macro on the tab developer button Macros (Developer - Macros) or by clicking Alt+F8. A file open dialog will be displayed, where you must specify one or more (hold down Ctrl or Shift) files, sheets of which must be added to the current book - and the task is solved!
- What are macros, where to insert macro code in Visual Basic
- Automatic assembly of given sheets from given books with the PLEX add-on
- Automatically collect data from multiple sheets into one summary sheet using the PLEX add-on