Generate a Monthly VBA Summary from Conditional Excel Data in WPS Office
Question details
The user needs a VBA macro in WPS Office to create or refresh a monthly summary table showing each month that contains red values, the count of matching records, and the associated person names.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a source table with monthly records, people, and color-coded values, and wants to automate the process of grouping red records by month into a summary sheet.
- Observed behavior
- The desired behavior is a refreshed output table that lists qualifying months, counts their red records, and combines the associated person names without manual filtering.
Confirm that the source table uses a consistent layout with identifiable Month, Person, and status or color columns. Save the workbook as a macro-enabled file so the VBA code and refresh button are preserved.
Build a VBA summary macro with a Dictionary and named table columns
Use this solution when the source data is in a named ListObject and you want a clean, reusable summary of red records grouped by month.
This macro reads the Month and Person columns from a named table, stores matching red records in a Scripting.Dictionary, and writes one row per month to a new range.
It avoids repeated worksheet scanning and is easier to maintain when column names remain unchanged.
In WPS Spreadsheets, click File > Save As, choose the macro-enabled workbook format such as .xlsm, and save the file.
Click the Developer tab and then click Macros, or press Alt+F11 to open the VBA editor.
In the Project Explorer, right-click VBAProject, select Insert > Module, and paste the summary macro code into the new module.
Update the macro to match your workbook, for example the Sheet1 worksheet, Table1 list object, and the Month and Person columns.
Press F5 to run the macro, or click Developer > Macros, select the macro, and click Run. Optionally insert a button and assign the macro to refresh the summary.

Refresh an existing output sheet by scanning cell fill colors
Use this solution when your output sheet already has headers and you want to update it in place based on red cell interior formatting.
Create a Monthly Red-Value Summary with VBA in WPS Spreadsheets
Use the WPS Spreadsheets VBA editor to scan a source table, group red records by month, collect the associated person names, and write a reusable summary. The process follows familiar Excel VBA logic and keeps your macro available in macro-enabled workbook format.
- 1. Enable the Developer tab: In WPS Spreadsheets, click File > Options > Customize Ribbon and enable the Developer tab to access macro tools.
- 2. Save as a macro-enabled workbook: Click File > Save As and select the macro-enabled workbook format (.xlsm) before adding VBA code.
- 3. Open the VBA editor and insert a module: Press Alt+F11, right-click the VBAProject in Project Explorer, and choose Insert > Module.
- 4. Paste and adjust the summary macro: Paste the VBA code and update the source sheet, table name, and column names such as Month and Person to match your data.
- 5. Run and assign the macro: Click Developer > Macros, select the macro, click Run, and optionally add a button that runs the macro when you need to refresh the summary.

Frequently Asked Questions
Why does my macro disappear after saving in WPS Spreadsheets?
VBA macros are only retained in a macro-enabled format. Save the workbook as .xlsm through File > Save As instead of .xlsx.
Can WPS Office run VBA macros written for Microsoft Excel?
WPS Spreadsheets supports many Excel VBA macros and opens macro-enabled workbooks, but some advanced ActiveX or Excel-specific APIs may behave differently. Test the macro on a copy first.
How do I assign the summary macro to a refresh button?
Enable the Developer tab, click Insert, choose a button control, draw it on the sheet, and assign the macro. After that, clicking the button runs the summary update.
Why are months with no red values not shown in the summary?
The sample code only writes a month when the red count is greater than zero. If you need every month to appear, remove or adjust the condition that checks for a positive count.
What does a subscript out of range error mean in this VBA summary?
It usually means the macro cannot find the specified sheet, table, or column. Check that names like Sheet1, Table1, Month, and Person exist in the workbook or change them in the code.




