The final macro is named ChangeFilter, and it is stored on a regular code module.
It shows that item, or shows “All”, if the previous item was at the beginning or end of the list. The code gets the current item number, then adds or subtracts 1, to get the new item number. Store this code in a regular code module. The code is in the sample file (on the modPivot module), and on the Report Filter Macros page of my website. Next, you’ll add two macros – PivotPageUp and PivotPageDown. In the screen shot below, you can see all the items in the Product field. The only field in the pivot table is Product, in the Report Filter area. In the sample file, the pivot table is on the same sheet, so it’s easier to see how the technique works. You can put the pivot table on the same worksheet, or on a different sheet. The macros will change the selected item in a pivot table’s report filter, and then change the selected item in the Excel Table’s Product column filter.Ĭreate a pivot table, based on the Excel table that you want to filter. Select the SpinUp and SpinDown procedures, and add two macro names in each procedure. Next, I added code to the Spin Button – right-click on it, and click View Code. In this screen shot, you can see the size of the Spin Button, compared to a Slicer for the Product field.įirst, I added an ActiveX Spin Button on the worksheet – there are detailed instructions on the Report Filter Macros page of my website. A Spin Button is a compact way to go through a list of items, in alphabetical order. Now you can use Slicers to filter a pivot table or Excel table, but they take up a lot of space on a worksheet. Slicers hadn’t been invented yet, and the spin button was a quick way to filter a pivot table. I wrote the original code for this technique long ago, to scroll through items in a pivot table report filter. When you reach the beginning or end of the list, the next selection is “All”. Click the Down button, to filter by the previous product in the list. Click the Up button, to filter by the next product in the list. This animated screen shot shows how the scrolling technique works. This technique uses a pivot table, which could be hidden on a different sheet, and a spin button, to go up or down in the list of items. Someone asked me if there was a way to scroll through the items, instead of opening the filter list each time. To see specific data in an Excel Table, you can select an item from the drop down filter in a column heading.
0 kommentar(er)
