How to Fix WPS Office Excel VBA Loop Through Sheets and Compare a Month Value
Question details
The user needs an Excel VBA macro that uses an InputBox value in YYYY-MM format to loop through worksheets, skip already updated sheets, and insert template rows only where the worksheet month value differs from the input.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user is working with a workbook that contains a Template sheet, a User List sheet, and multiple data sheets. The goal is to automate row insertion by comparing each sheet's A11 month value with a YYYY-MM value entered through an InputBox.
- Observed behavior
- The macro should skip the Template and User List sheets, compare the InputBox value with each worksheet A11 value as a String, and insert template rows 11:12 only on sheets whose month value does not match the entered month.
Before editing the macro, confirm that the workbook contains a sheet named "Template" with rows 11:12 formatted as desired and a sheet named "User List" to exclude. Also verify that cell A11 in each target worksheet contains a comparable month value and that the InputBox value is entered in YYYY-MM format.
Store the InputBox as a String and Compare It Directly in WPS Excel VBA
Use this solution when your VBA macro needs to insert template rows only on worksheets whose A11 month differs from the YYYY-MM value entered in an InputBox.
The key is to keep the InputBox result as a String and compare it with the worksheet cell value using CStr(), instead of applying .Value to the String variable. This avoids type mismatch errors and ensures the macro compares the month values correctly.
In WPS Office Excel, press Alt+F11 to open the Microsoft Visual Basic for Applications editor, then locate the module that contains your macro.
Use Dim NewMonth As String and assign NewMonth = InputBox("Enter month in YYYY-MM format:") after validating that the input looks like YYYY-MM.
Before looping, check the input with If NewMonth Like "####-##" Then. If it does not match, show a message and exit or prompt again.
Use For Each Ws In ThisWorkbook.Worksheets and skip the excluded sheets with If Ws.Name <> "Template" And Ws.Name <> "User List" Then. Compare with If CStr(Ws.Range("A11").Value) <> NewMonth Then.
When the condition is true, insert two rows at row 11 with Ws.Rows(11).Resize(2).Insert Shift:=xlDown, then copy Template rows 11:12 with ThisWorkbook.Worksheets("Template").Rows("11:12").Copy Ws.Rows(11).
Use WPS Office Excel VBA to Loop Through Sheets and Compare a YYYY-MM Month Value
Accomplish this task directly in WPS Office Excel. Keep the InputBox result as a String, validate the YYYY-MM format, loop through ThisWorkbook worksheets, skip the Template and User List sheets, and insert template rows only where the A11 month does not match.
- 1. Open the workbook in WPS Office Excel: Launch WPS Office and open the workbook that contains the Template sheet, User List sheet, and the worksheets to loop through.
- 2. Enter the VBA editor: Press Alt+F11 in WPS Office Excel to open the VBA editor and select the module where the macro is stored.
- 3. Edit the VBA code: Add or update the code to use Dim NewMonth As String, validate the YYYY-MM format, and compare CStr(Ws.Range("A11").Value) <> NewMonth inside the worksheet loop.
- 4. Run the macro: Press F5 or choose Run > Run Sub/UserForm in the VBA editor, then enter the month in YYYY-MM format when the InputBox appears.
- 5. Verify the inserted rows: Check that rows 11:12 are inserted from Template only on sheets whose A11 month differs from the input, while Template and User List remain unchanged.
Frequently Asked Questions
Why should I not use .Value on the InputBox result when comparing the month?
Because the InputBox result is already a String. Applying .Value to a String variable can cause an error or incorrect comparison. Compare the worksheet value with CStr(Ws.Range("A11").Value) <> NewMonth instead.
How do I skip the Template and User List worksheets in the loop?
Inside the For Each Ws In ThisWorkbook.Worksheets loop, add If Ws.Name <> "Template" And Ws.Name <> "User List" Then before checking cell A11.
How do I validate that the InputBox value is in YYYY-MM format?
Use If NewMonth Like "####-##" Then before the loop. If the input does not match the pattern, show a message and exit or prompt the user again.
Why should I use ThisWorkbook instead of ActiveWorkbook?
ThisWorkbook refers to the workbook that contains the VBA code. It prevents the macro from accidentally modifying a different workbook that may be active when the macro runs.
Will this VBA macro work in WPS Office with Microsoft Excel files?
Yes, WPS Office supports VBA macros and opens .xlsm and .xlsx workbooks. However, macro security settings or missing Excel-specific object references may need to be checked if the macro does not run.
