VBA & Macro Problems

How to Exclude Specific Worksheets When Combining Sheets with Excel VBA

Chanuka GeekiyanageChanuka Geekiyanage Nov 2, 2026 871 views

Question details

The user needs to exclude specific worksheets, such as 'Combined' and 'Summary', from a VBA macro loop that merges data from all sheets.

How to Exclude Specific Worksheets When Combining Sheets with Excel VBA
Product
Excel
Device & OS
not provided
Scenario
Running a VBA script to loop through all worksheets and append their data into a single master sheet.
Observed behavior
The macro inadvertently copies data from the newly created 'Combined' sheet and other structural sheets like 'Summary', resulting in duplicated or incorrect data sets.
Before you start

Open your Excel workbook and press ALT + F11 to access the VBA Editor and locate your active sheet-combining macro.

Solution 1Recommended

Modify the VBA Loop Condition with an If Statement

Update the worksheet iteration loop in your VBA script to actively skip over the specific sheets you do not want combined.

When a macro iterates through all worksheets using a loop like 'For Each s In Worksheets', it natively includes newly created sheets (like your 'Combined' master sheet) and other structural sheets (like 'Summary'). If you do not explicitly filter these out, their data gets recursively appended.

Using the inequality operator (<>) combined with the 'And' logical operator ensures that the code's copy-paste functions only execute on your target data sheets.

1
Locate the Loop

In the VBA Editor, find the line where your macro begins looping through the sheets. This typically looks like 'For Each s In ActiveWorkbook.Worksheets'.

2
Add the Exclusion Condition

Immediately after the loop starts, insert your conditional statement: 'If s.Name <> "Combined" And s.Name <> "Summary" Then'.

3
Enclose the Copy Action

Place an 'End If' statement right before the 'Next s' line to ensure your data copying commands only happen when the condition is met.

4
Run and Test

Press F5 to run the macro and verify that data from the excluded sheets is no longer appended to the master worksheet.

Modify the VBA Loop Condition with an If Statement
Header Row Verification: Ensure your macro copies the header row from a valid source worksheet to the Combined sheet exactly once before starting the data-appending loop.
Seamless Macro Execution

Run and Edit Excel VBA Macros in WPS Office

WPS Office provides robust built-in support for VBA macros, allowing you to combine worksheets and automate repetitive data tasks effortlessly.

  1. 1. Open Macro Workbook: Launch WPS Spreadsheet and open your existing .xlsm macro-enabled file.
  2. 2. Access Developer Tools: Navigate to the 'Developer' tab on the main application ribbon.
  3. 3. Open the VBA Editor: Click on 'Visual Basic' or press ALT + F11 to view and modify your macro code.
  4. 4. Execute the Macro: Click the 'Run' icon or press F5 to execute your worksheet-combining script instantly.
High compatibility with Microsoft Excel (.xlsm) formats and standard VBA scripts.Built-in VBA editor to debug and refine your sheet-combining macros seamlessly.Lightweight performance capable of processing large datasets quickly.
microsoft office alternative - wps office

Frequently Asked Questions

How do I ensure headers are copied only once in my VBA macro?

To avoid duplicating headers on the master sheet, explicitly copy the header row from the first data sheet before entering your 'For Each' loop, and then configure your loop's copy range to start from the second row (e.g., Range("A2")).

Why is my VBA loop copying blank rows?

Your copy range might be dynamically selecting the entire worksheet, including empty cells. Use 'ws.UsedRange' or dynamically find the last populated row using 'ws.Cells(ws.Rows.Count, "A").End(xlUp).Row' to strictly limit the copied selection to cells with data.

Can I exclude sheets based on a partial name match in VBA?

Yes, you can use the 'Like' operator in your conditional statement. For example, 'If Not s.Name Like "*Summary*" Then' will exclude any worksheet that contains the word 'Summary' in its name, regardless of what characters come before or after it.