Copy Multiple Excel Worksheets into a Master Sheet with VBA
Question details
The user needs to append data from any number of worksheets to a new master sheet using VBA.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A recorded macro currently copies data from a fixed number of worksheets and needs to handle additional sheets automatically.
- Observed behavior
- The existing macro is limited to the worksheets specified in its code.
Open the source workbook in desktop Excel and make sure its worksheets contain the data you want to combine. The macro copies each nonempty sheet's used range, including headers, formulas, and formatting.
Loop through worksheets and append each used range
Use a For Each loop to copy data from every nonempty worksheet in the workbook containing the macro into a new workbook.
Each copied range starts in column A directly below the preceding range. UsedRange can include cells that appear empty but retain formatting, so check the result if the master sheet contains unexpected blank rows or columns.
In the source workbook, select Developer > Visual Basic, or press Alt+F11 where supported. In the VBA editor, select the source workbook and choose Insert > Module.
Paste this code into the new module: Sub CopySheetsToMaster() Dim destWksht As Worksheet Dim wksht As Worksheet Dim sourceRange As Range Dim nextRow As Long Set destWksht = Workbooks.Add(xlWBATWorksheet).Worksheets(1) nextRow = 1 For Each wksht In ThisWorkbook.Worksheets If Not wksht.Cells.Find(What:="*", LookIn:=xlFormulas, _ SearchOrder:=xlByRows, SearchDirection:=xlNext) Is Nothing Then Set sourceRange = wksht.UsedRange sourceRange.Copy Destination:=destWksht.Cells(nextRow, 1) nextRow = nextRow + sourceRange.Rows.Count End If Next wksht End Sub
Place the cursor inside the macro and select Run > Run Sub/UserForm, or press F5. Review the new workbook, then use File > Save As to save the master sheet.
To copy the same fixed range from every nonempty sheet instead, replace wksht.UsedRange in the Set sourceRange line with wksht.Range("A2:C8"), adjusting the address for your data.

Work with Excel Workbooks in WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move existing workbooks into WPS Spreadsheet. VBA availability and compatibility can vary by WPS edition, so test macros before relying on them.

Frequently Asked Questions
Will the macro include worksheets added later?
Yes. The For Each loop checks ThisWorkbook.Worksheets each time you run it, so newly added worksheets in the workbook containing the macro are included.
How do I copy a fixed range instead of each used range?
In the VBA editor, replace wksht.UsedRange in the Set sourceRange line with a range such as wksht.Range("A2:C8"). The macro will copy that range from each nonempty worksheet.
Why are there repeated headers in the master sheet?
The macro copies each sheet's entire used range, including its header. To exclude headers, change the source range in the VBA code so it starts below the header row.
Does the macro copy data from hidden worksheets?
Yes. ThisWorkbook.Worksheets includes hidden sheets. Unhide and review them before running the macro, or modify the loop to check each worksheet's Visible property.
Where is the master sheet created?
The Workbooks.Add command creates a new workbook, and its first worksheet becomes the master sheet. Use File > Save As to save that new workbook after reviewing the copied data.




