Move a PivotTable Item to the Top Using a Slicer and VBA
Question details
The user wants to dynamically reorder a PivotTable so that an item selected via a Slicer immediately moves to the first position (the top) of the PivotTable layout.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Reorganizing data in a PivotTable dynamically based on user interaction with a Slicer to highlight the selected item.
- Observed behavior
- The user needs to implement a VBA macro that listens for slicer changes and automatically shifts the corresponding PivotItem to position 1.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that Developer tools and macros are enabled in your spreadsheet application.
Use a VBA Worksheet_Change Event to Sort the PivotTable
Apply a VBA macro triggered by a Worksheet_Change event to automatically reposition the PivotItem selected by your Slicer.
To make this work, the PivotTable row field must be set to manual sorting. The VBA code relies on identifying the slicer's active value from a specific cell (e.g., cell B6) and updating the PivotTable item's position property to 1.
Right-click the target row field in your PivotTable, navigate to 'Sort', and select 'More Sort Options'. Choose 'Manual (you can drag items to rearrange them)' and click OK.
Right-click the sheet tab containing your PivotTable at the bottom of the window and select 'View Code' to open the VBA Editor for that specific worksheet.
Paste the following code into the code window: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B6")) Is Nothing Then Application.EnableEvents = False Me.PivotTables("PivotTable1").PivotFields("PDT").PivotItems(Range("B6").Value).Position = 1 Application.EnableEvents = True End If End Sub
Modify 'PivotTable1' and 'PDT' to match your PivotTable name and field name. Change 'B6' to the cell reference where your slicer selection value is displayed or tracked.
Close the VBA editor. Click an item in your Slicer, and watch the corresponding item move to the top of the PivotTable.
Manage PivotTables and Macros with WPS Spreadsheet
WPS Spreadsheet offers comprehensive support for PivotTables, Slicers, and VBA macros, empowering you to create dynamic, interactive, and automated reports seamlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open your data file containing the PivotTable.
- 2. Insert a Slicer: Navigate to the Insert tab and select 'Slicer' to connect an interactive filter to your PivotTable.
- 3. Access the Developer Tools: Go to the Developer tab, click on 'VB Editor', and input your automation macros to enhance your PivotTable's behavior.
- 4. Save with Macros: Save your document as an .xlsm (Macro-Enabled Workbook) to ensure your VBA code is preserved for future use.
Frequently Asked Questions
Why doesn't my PivotTable update when I click the slicer?
Ensure that Macros are enabled in your spreadsheet's trust center settings. Additionally, verify that the Worksheet_Change event code is pasted in the specific Sheet module (e.g., Sheet1) rather than a standard module, and that your target cell reference is correct.
How do I link a slicer's output to a specific cell like B6?
A standard slicer filters a PivotTable directly and does not automatically output to a single cell. You can connect a dummy PivotTable to the same slicer and reference the resulting filtered cell, or use a CUBE function to extract the selected item into cell B6.
Will this macro work if multiple items are selected in the slicer?
The provided code uses 'Range("B6").Value', which expects a single string value. If multiple items are selected, cell B6 might display a '(Multiple Items)' label, causing the macro to throw an error since there is no PivotItem named '(Multiple Items)'. You will need to add error handling to bypass the macro during multi-selections.
