How to List Worksheet Names Matching a Pattern with Excel VBA in WPS Spreadsheets
Question details
The user needs to write a VBA macro in WPS Spreadsheets that lists worksheet names matching a pattern, while avoiding filling cells with TRUE or FALSE values.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a workbook with multiple worksheets and needs to extract only the worksheet names that match a pattern such as "* (M)".
- Observed behavior
- Assigning Sheets(i).Name Like "* (M)" directly writes TRUE or FALSE to cells instead of the matching worksheet names.
Enable the Developer tab and make sure macros are enabled in WPS Spreadsheets, because the VBA code will not run if macro execution is blocked.
Use an If Statement with the Like Operator to Write Only Matching Worksheet Names
This is the recommended approach because the Like operator returns a Boolean value, so you must test it in an If statement before writing a result.
The Like operator compares each worksheet name to a pattern and returns True or False. Wrapping it in an If statement lets the macro write the sheet name to the target column only when the pattern matches.
In WPS Spreadsheets, click the Developer tab, then select Visual Basic or Macro Editor to open the VBA editing window.
In the VBA editor, click Insert > Module, then paste the following macro: Sub List_Worksheets() Dim i As Long, r As Long: Application.ScreenUpdating = False: Range("AC:AC").ClearContents: For i = 1 To Sheets.Count - 3: If Sheets(i).Name Like "* (M)" Then: r = r + 1: Range("AC" & r).Value = Sheets(i).Name: End If: Next i: Application.ScreenUpdating = True: End Sub
Press F5 or click Run > Run Sub/UserForm in the VBA editor, switch back to the worksheet, and check column AC for the matching worksheet names.
List Matching Worksheet Names with VBA in WPS Spreadsheets
WPS Spreadsheets supports VBA macros and the Like operator, so you can run the same Excel VBA logic to filter worksheet names by a pattern and write only the matching names to your workbook.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the macro-enabled workbook or any workbook where you want to list matching sheet names.
- 2. Enable the Developer tab: Go to File > Options > Customize Ribbon and enable the Developer tab if it is not already visible.
- 3. Create and run the VBA macro: On the Developer tab, click Visual Basic, insert a module, paste the macro with If Sheets(i).Name Like "* (M)" Then, and press F5 to run it.
- 4. Check the matching worksheet list: Return to the worksheet and confirm that column AC shows only the worksheet names matching the pattern.
Frequently Asked Questions
Why does Sheets(i).Name Like "* (M)" show TRUE or FALSE in Excel or WPS?
The Like operator returns a Boolean value, True or False. To list worksheet names, test the condition in an If statement and write the worksheet name only when the condition is True.
Can I change the pattern to match different worksheet names?
Yes. Replace "* (M)" with another pattern such as "Sales*" or "*Report". In VBA, * matches any number of characters and ? matches a single character.
Does this macro work in WPS Spreadsheets?
Yes. WPS Spreadsheets supports VBA macros. Open the Developer tab, click Visual Basic, insert a module, paste the code, and run the macro.
How do I list worksheet names in a different column or worksheet?
Change the target range in the macro. For example, replace Range("AC:AC") and Range("AC" & r) with your desired worksheet and column reference.
Why are no worksheet names appearing after running the macro?
Make sure macros are enabled in WPS Spreadsheets and that the pattern matches an existing worksheet name. Check for extra spaces, parentheses, or capitalization differences in the sheet names.
