VBA & Macro Problems

How to Run an Excel VBA Macro Only on Selected Project Sheets

John Wilson Oct 15, 2026 869 views

Question details

The user wants to execute a VBA macro only on specific worksheets within a workbook that contain necessary named ranges, completely avoiding execution on ancillary sheets.

Product: Excel
Device & OS: not provided
Scenario: Looping through multiple worksheets in a workbook to process data while skipping non-project sheets that lack the required named ranges.
Observed behavior: The macro throws errors or breaks when attempting to reference sheet-level named ranges on unqualified or incorrect sheets.

Before you start

Verify that all target project worksheets contain the properly defined sheet-level named ranges (e.g., 'copypaste.chk', 'FinCo.paste') before modifying and running your VBA macro.

Solution 1Recommended

Filter Sheets with InStr and Qualify Range References

Use a For Each loop to evaluate worksheet names, and pass the matched worksheet object to a sub-procedure to explicitly qualify all range references.

Simply selecting a sheet during a macro does not guarantee that sheet-level named ranges will resolve correctly. By passing the worksheet object directly to the procedure, you can explicitly qualify every range reference to prevent the macro from breaking on ancillary sheets.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications (VBA) editor in your spreadsheet program.

Create the Main Subroutine

Insert a new Module and declare your main looping subroutine (e.g., Sub SizeAllDebt()). Add an error handling line like 'On Error GoTo CleanUp' to manage unexpected breaks.

Loop and Filter Sheet Names

Use 'For Each ws In ThisWorkbook.Worksheets' to iterate. Inside the loop, add a condition such as 'If InStr(1, ws.Name, "Site", vbTextCompare) > 0 Then' to identify specific project sheets by their naming convention.

Pass the Worksheet Object

When a matching sheet is found, call your processing sub-procedure and pass the worksheet object to it (e.g., 'IterateCircularities2 ws').

Qualify Ranges in the Sub-procedure

In the processing sub-procedure, declare 'ByVal ws As Worksheet'. Ensure every range call is prefixed with 'ws.' (e.g., ws.Range("copypaste.chk")) so it targets only that specific sheet.

Filter Sheets with InStr and Qualify Range References

Cleanup and Screen Updating: Always include a CleanUp block at the end of the main macro to restore Application.ScreenUpdating = True, ensuring the application interface unfreezes after execution or in case of an error.

Advanced Spreadsheet Management

Write and Run Macros Seamlessly with WPS Spreadsheet

WPS Office provides robust support for VBA macros, allowing you to automate repetitive tasks, loop through worksheets, and manage complex financial data just like you would in Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Office and open your macro-enabled workbook.
2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon.
3. Open the VBA Editor: Click on 'Visual Basic' or 'Macros' to launch the built-in VBA code editor.
4. Insert Looping Code: Paste your customized 'InStr' filtering loops and explicit worksheet references.
5. Run the Macro: Execute the macro to process your specific project sheets with high performance.

Fully compatible with Microsoft Excel macro files (.xlsm and .xlsb)Built-in VBA editor for writing, editing, and debugging codeLightweight architecture for faster execution on heavy workbooksCost-effective alternative with advanced automation capabilities

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro break when referring to sheet-level named ranges?

This commonly happens because range references implicitly point to the currently active sheet if they aren't qualified. Explicitly qualifying the reference with a specific worksheet object (e.g., ws.Range("Name")) ensures the code targets the correct sheet and resolves successfully.

How does the InStr function work to filter worksheet names?

The InStr function searches for a specific substring within a main string. By using 'InStr(1, ws.Name, "TargetWord", vbTextCompare)', VBA checks if the worksheet's name contains 'TargetWord'. If it returns a value greater than 0, the sheet is a match, allowing you to run the macro conditionally.

What is the benefit of passing a worksheet object to a sub-procedure?

Passing a worksheet object (ByVal ws As Worksheet) isolates the sub-procedure, making it highly reusable and modular. It ensures all data processing inside the sub-procedure occurs strictly on the passed worksheet, regardless of which sheet a user might click on during execution.

How do I prevent Excel from freezing if my macro hits an error?

When you set Application.ScreenUpdating = False to speed up a macro, an unhandled error can leave the interface frozen. Include an 'On Error GoTo CleanUp' line at the beginning, and a 'CleanUp:' label at the end where you reset Application.ScreenUpdating = True.

Excel & Data Problems