How to Use VBA to Filter WPS Spreadsheets Pivot Slicers to a Rolling Six-Month Period
Question details
The user needs to create a VBA command button that refreshes WPS Spreadsheets PivotTables and filters the connected date slicers so multiple worksheets show only the most recent six months.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages dashboards with PivotTables and slicers spread across several worksheets and wants to automate a rolling six-month view after the source data changes.
- Observed behavior
- Date slicers currently require manual adjustment after refresh, so the worksheets can show an outdated or inconsistent six-month window.
Before adding VBA, confirm that all PivotTables and slicers are based on the same source-data date field and that the date column is formatted as a real date in WPS Spreadsheets.
Use a VBA Command Button to Apply a Rolling Six-Month Slicer Filter
This is the most reliable approach when you want one button to refresh all PivotTables and update each date slicer to the latest six-month window.
The exact VBA code depends on your PivotTable names, slicer cache names, and source-data date field. The routine should be adapted to the names in your workbook, and all date fields must be consistent so the slicer can filter each PivotTable correctly.
In WPS Spreadsheets, open the ribbon options to enable the Developer tab, then click Insert > Command Button and draw the button on the worksheet that will control the filter.
Right-click the command button and choose View Code, or press Alt+F11 to open the VBA editor and select the sheet module where the button was added.
Write a subroutine that captures the latest date, calculates DateSerial(Year(Date), Month(Date) - 5, 1) as the start of the six-month window, refreshes each PivotTable by name, and then assigns the visible items to each SlicerCache date field.
Return to WPS Spreadsheets, click the command button, and verify that the PivotTables refresh and every date slicer displays only the most recent six-month period.
Create a Rolling Six-Month Filter with a Source-Data Helper Column
Use this when you prefer not to use VBA or need a quick manual alternative while you build the macro.
Use WPS Spreadsheets to Run VBA for Rolling Six-Month Pivot Slicers
WPS Spreadsheets supports the VBA editor and macro-enabled workbooks, so you can add a command button, refresh PivotTables, and filter date slicers to the latest six months without switching to a paid Office suite.
- 1. Open the macro-enabled workbook: Launch WPS Spreadsheets and open the .xlsm file that contains the PivotTables and slicers you want to automate.
- 2. Insert a command button: Enable the Developer tab, click Insert > Command Button, and draw the button on the sheet that should control the rolling filter.
- 3. Open the VBA editor and add the filter code: Right-click the button and choose View Code, then create a subroutine that refreshes the PivotTables and applies the DateSerial rolling six-month range to each SlicerCache.
- 4. Run the macro and confirm the slicers: Click the button in the worksheet and verify that all selected worksheets show only the most recent six months.
- 5. Save as a macro-enabled workbook: Save the file as a macro-enabled workbook type so the VBA button remains available for future refreshes.
Frequently Asked Questions
Why does the slicer not change to the rolling six months after I run the macro?
Usually the macro applies the filter before the PivotTable is refreshed, or the SlicerCache name does not match the code. Refresh the PivotTable first, then assign the visible items to the correct SlicerCache, and make sure the source range includes the latest dates.
Which VBA function should I use to calculate the six-month start date?
Use DateSerial(Year(Date), Month(Date) - 5, 1) to return the first day of the month five months before the current month, giving a six-month window when combined with the latest date.
Can one VBA button update slicers on multiple worksheets in WPS Spreadsheets?
Yes. The button can loop through the worksheet or PivotTable names, refresh each one, and apply the same date range to each connected slicer cache as long as the date field exists in all source data.
What should I check if WPS Spreadsheets hides the Developer tab?
Enable it from the ribbon options or Settings: right-click the ribbon, choose Customize Ribbon, and select Developer so the Insert > Command Button controls and VBA editor are available.
