WPS Spreadsheets VBA Macro to Search Monthly Reports and Build a Consolidation
Question details
The user needs a VBA macro that selects a source workbook, searches monthly profit and loss and balance sheet worksheets, finds the latest reporting month, and copies required values into a consolidation sheet.
- Product
- WPS Spreadsheets VBA
- Device & OS
- not provided
- Scenario
- A consolidation workbook must collect metrics such as Sales and Cost of Sales from worksheets named with PL_* or BS_* patterns across a selected monthly report workbook.
- Observed behavior
- The macro workflow is not yet complete: it must identify the correct reporting month, locate labels and values, map results to destination cells, and handle missing files, sheets, labels, or dates.
Confirm the source workbook uses consistent worksheet names such as PL_CompanyA or BS_CompanyA, with report months in a predictable header row. Define the destination cells and the source column containing each metric before opening the VBA editor.
Build a VBA macro that opens the report, finds the latest month, and consolidates metrics
Use this approach when monthly workbooks follow consistent sheet, label, and header conventions and the results must be written automatically to a summary sheet.
The macro should use GetOpenFilename to let the user select a workbook, open it read-only, and loop through worksheets matching PL_* or BS_*. Use Find on column A for each required label, then read the value from the relevant column or offset. Add validation before writing results so an absent sheet, label, month, or file does not produce incorrect totals.
In WPS Spreadsheets, open the consolidation workbook, use Tools > Macro > Visual Basic Editor or press Alt+F11, choose Insert > Module, and create a standard module for the macro.
Use Application.GetOpenFilename with an Excel file filter, check that the result is not False, and open the selected file with Workbooks.Open(FileToOpen, ReadOnly:=True). Keep the consolidation workbook referenced with ThisWorkbook.
Loop through OpenBook.Worksheets and test ws.Name Like "PL_*" Or ws.Name Like "BS_*". For each matching sheet, use ws.Columns("A").Find(What:="Sales", LookIn:=xlValues, LookAt:=xlWhole) and repeat for labels such as Cost of Sales; read the matching value from foundRange.Offset(0, 2) when the value is in column C.
Read the report header range, convert valid header values with IsDate, and retain the greatest date. Compare each metric's month with that date before copying it to the consolidation sheet.
Map each company, worksheet type, metric, and month to its destination range, write only validated values, close the source workbook with OpenBook.Close SaveChanges:=False, and restore Application.ScreenUpdating = True in both normal and error-handling paths.
Use a structured mapping table for multiple companies and metrics
Use this variation when the consolidation workbook contains many companies or metrics and hard-coded destination cells would be difficult to maintain.
Automate monthly report consolidation with WPS Spreadsheets VBA
WPS Spreadsheets can organize the consolidation workbook, run a compatible VBA workflow where macro support is available, and preserve the familiar spreadsheet structure while you select source reports, search PL and BS sheets, and populate summary cells.
- 1. Install WPS Office: Download and install WPS Office, then open the consolidation workbook in WPS Spreadsheets.
- 2. Enable macro functionality: Open the workbook's macro or security settings from the Tools or Options menu and allow macros only for trusted files and locations.
- 3. Add the consolidation macro: Use Tools > Macro > Visual Basic Editor or Alt+F11, insert a standard module, and add the workbook-selection, worksheet-search, month-detection, and destination-mapping logic.
- 4. Run and verify the macro: Return to the spreadsheet, use Tools > Macro > Macros, select the consolidation procedure, and verify the latest-month values and missing-item checks on the summary sheet.
Frequently Asked Questions
How can the macro let me choose the monthly report workbook?
Use Application.GetOpenFilename with an Excel file filter, check whether the returned value is False, and pass the selected path to Workbooks.Open with ReadOnly:=True.
How do I search only PL and BS worksheets?
Loop through OpenBook.Worksheets and test each name with ws.Name Like "PL_*" Or ws.Name Like "BS_*" before searching for labels.
How can VBA find Sales or Cost of Sales in a report?
Search the required sheet's column A with Find, using LookIn:=xlValues and LookAt:=xlWhole. If the result is not Nothing, read the value from the required column or from foundRange.Offset(0, 2).
What should happen when a label or worksheet is missing?
Check every worksheet and Find result before reading values. Write a clear status such as Missing sheet or Missing label to an Errors sheet rather than copying a blank or treating it as zero.
How can the macro select the latest reporting month?
Scan the report header range, use IsDate to identify valid month headers, convert them to dates, and retain the greatest date before processing the matching metric values.
