How to combine multiple worksheets into one in Excel? (using vba)
I have several excel workbooks (.xls files) and each workbook contains multiple worksheets. The number of worksheets (as well as their names) are not constant. Each worksheet is formatted in the same way (i.e: same header). Now I want to copy all the data on each worksheet and copy it into a single worksheet.
Launch an Excel file that you want to combine other workbooks into. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module.
Oh, and before that, create a sheet called "Summary" where we will collect the info.
Sub SummarizeSheets()
Dim ws As Worksheet
Application.ScreenUpdating = False
Sheets("Summary").Activate
Path = "C:\path\to\your\files\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
For Each ws In Worksheets
If ws.Name <> "Summary" Then
ws.Range("A01:AX201").Copy
ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)
End If
Next ws
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
Then click doc-merge-multiple-workbooks-button button to run the code, and all of the worksheets (including the blank worksheets) within the workbooks have been merged into the master workbook.
References: http://www.extendoffice.com/documents/excel/456-combine-multiple-workbooks.html