How to Exclude Specific Worksheets When Combining Sheets with Excel VBA
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.

- 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.
Open your Excel workbook and press ALT + F11 to access the VBA Editor and locate your active sheet-combining macro.
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.
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'.
Immediately after the loop starts, insert your conditional statement: 'If s.Name <> "Combined" And s.Name <> "Summary" Then'.
Place an 'End If' statement right before the 'Next s' line to ensure your data copying commands only happen when the condition is met.
Press F5 to run the macro and verify that data from the excluded sheets is no longer appended to the master worksheet.

Use Select Case for Multiple Sheet Exclusions
If you need to exclude several sheets, a Select Case statement provides cleaner and more readable code than a long chain of If-And statements.
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. Open Macro Workbook: Launch WPS Spreadsheet and open your existing .xlsm macro-enabled file.
- 2. Access Developer Tools: Navigate to the 'Developer' tab on the main application ribbon.
- 3. Open the VBA Editor: Click on 'Visual Basic' or press ALT + F11 to view and modify your macro code.
- 4. Execute the Macro: Click the 'Run' icon or press F5 to execute your worksheet-combining script instantly.

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.




