VBA & Macro Problems

Copy Multiple Excel Worksheets into a Master Sheet with VBA

Ayan MasoodAyan Masood Nov 18, 2026 868 views

Question details

The user needs to append data from any number of worksheets to a new master sheet using VBA.

Copy Multiple Excel Worksheets into a Master Sheet with 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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

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.

2
Add the macro

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

3
Run and save the result

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.

4
Adjust the source range if needed

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.

Loop through worksheets and append each used range
Check headers and formulas: The macro copies headers from every source range and includes hidden worksheets. Copied formulas can change their references according to Excel's normal copy behavior.
Free Microsoft Office alternative

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.

Open and edit Microsoft Excel XLSX and XLS filesWork with data from multiple worksheets in a familiar interfaceUse a free, lightweight office suite for everyday spreadsheet tasksMove existing Office files into a familiar workspace
QA img-9

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.