VBA & Macro Problems

Write an Excel VBA Macro to Combine Specific Worksheets into a Master Sheet

Khadija Khan Nov 12, 2026 869 views

Question details

Create a VBA macro that combines the same cell range from only specified worksheets into a single Master worksheet.

Write an Excel VBA Macro to Combine Specific Worksheets into a Master Sheet

Product: Microsoft Excel VBA
Device & OS: not provided
Scenario: The user needs to copy A2:CQ225 from a defined list of source sheets and append the data to a master data sheet.
Observed behavior: Only worksheets whose names appear in the permitted list should be processed; sheets such as Master and other excluded names should not be copied.

Before you start

Make sure the Master worksheet already exists and that every source worksheet uses the same column layout in A2:CQ225. Update the permitted sheet names in the macro so they exactly match the worksheet tabs.

Solution 1Recommended

Use a permitted worksheet-name list to combine only selected sheets

Use this approach when you need to process a fixed set of worksheets and ignore every other worksheet in the workbook.

The macro checks each worksheet name against an allowed list. When the name matches, it copies A2:CQ225 and appends the values below the existing data on the Master sheet.

Open the Visual Basic Editor

In Excel, press Alt+F11, then select Insert > Module to create a standard VBA module.

Paste the allowed-list macro

Paste this code into the module: Sub CombineSpecifiedSheets() Dim ws As Worksheet Dim masterWs As Worksheet Dim allowedSheets As Variant Dim sheetName As Variant Dim isAllowed As Boolean Dim nextRow As Long allowedSheets = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5") Set masterWs = ThisWorkbook.Worksheets("Master") For Each ws In ThisWorkbook.Worksheets isAllowed = False For Each sheetName In allowedSheets If ws.Name = CStr(sheetName) Then isAllowed = True Exit For End If Next sheetName If isAllowed Then nextRow = masterWs.Cells(masterWs.Rows.Count, "A").End(xlUp).Row + 1 ws.Range("A2:CQ225").Copy masterWs.Cells(nextRow, "A").PasteSpecial xlPasteValues End If Next ws Application.CutCopyMode = False End Sub

Replace the worksheet names

Edit the Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5") line and enter the exact names of the worksheets you want to combine.

Run the macro

Return to Excel, press Alt+F8, select CombineSpecifiedSheets, and click Run.

Use a permitted worksheet-name list to combine only selected sheets

Master sheet handling: Because Master is not included in the allowedSheets array, the macro will not copy data from the Master sheet into itself.

Solution 2

Exclude a small number of worksheets by name

Use this shorter condition when most worksheets should be combined and you only need to skip Master and a few named exceptions.

Work with spreadsheets more efficiently

Combine selected worksheet ranges with macros in WPS Spreadsheets

WPS Spreadsheets provides a familiar worksheet workspace for organizing source sheets and a master data sheet. You can use its macro tools to manage repeated data-combining tasks while working with Excel-compatible workbook formats.

1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the workbook containing the source sheets and Master sheet.
2. Check the Master worksheet layout: Select the Master tab and confirm that column A is available for appending the copied records.
3. Access macro tools: Open the Developer or Macro tools available in WPS Spreadsheets and create or manage the workbook macro.
4. Set the source sheet names and range: Use the permitted worksheet names and set the source range to A2:CQ225 so only the intended data is combined.
5. Save in a compatible format: Choose File > Save As and save the workbook as an Excel Workbook (.xlsx) or Excel Macro-Enabled Workbook (.xlsm) when macro content is required.

Organize selected worksheet data into one Master sheetUse a familiar spreadsheet interface for managing workbook tabs and rangesOpen and save Microsoft Excel .xlsx and .xls filesWork with macro-enabled Excel workbook formats such as .xlsmLightweight office suite for everyday spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

How do I combine only five specific worksheets in VBA?

Create an Array containing the five exact worksheet names, then loop through ThisWorkbook.Worksheets and copy the range only when ws.Name matches an item in that array.

Why should I use an allowed list instead of excluding sheet names?

An allowed list is safer when only a few sheets should be included. New worksheets added later will be ignored unless you explicitly add their names to the allowedSheets array.

How can I stop the macro from copying the Master sheet?

Do not include "Master" in the allowedSheets array. If you use exclusions instead, add ws.Name <> "Master" to the If condition before the copy command.

Where does the next copied range go on the Master sheet?

The line masterWs.Cells(masterWs.Rows.Count, "A").End(xlUp).Row + 1 finds the next empty row below the last used cell in column A, then pastes the new data starting in that row.

Can I combine a different range than A2:CQ225?

Yes. Replace ws.Range("A2:CQ225") with the required source range, such as ws.Range("A2:F500"), and ensure all selected worksheets use a compatible layout.

Excel & Data Problems