For example, we have a lot of Excel workbooks, and we want to collect the all worksheet from the different workbook into one file. So open the workbook, where we want to insert data from another file, then open Visual Basic Editor (Alt+F11) and add new blank module (Insert - Module) and copy this macros text to 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 "Have been chosen no files!" 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 we may return to Excel and run new macros through the menu Tools - Macro - Macros. There display dialog box, where need to specify one or several (holding Ctrl) files, worksheets from which we want to add to the current workbook.
If you use another extension of Excel file (i.e. *.xlsx, *xlsm, *.xlsb, etc.) you have to do appropriate changes to seventh line of the macros code.