Automating Pivot Table Filter Selection

blood_Milk_sky · Apr 28, 2017

Hi,

I have just recorded the below macro to go through and select each account number on a pivot table filter. I was just wondering if anyone knew any code that would do the below for all values in the filter instead of me going through and recording it ac number by account number?

the reason for this is that each time I run this there wont always be the same number of accounts or account numbers in the pivot table filter.

any help would be greatly appreciated.

many thanks

BMS

Code:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        CurrentPage = "120000TST"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        CurrentPage = "120003"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        CurrentPage = "120005"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID"). _
        CurrentPage = "120008003"
End Sub

sijpie · May 2, 2017

I don't know the structure of your dataset. I am assuming these business partner id's ar in one column of the data tabel and you want to loop through each unique BP ID.

When the word 'unique' appears, think 'collection'. A Collection is a dataformat in VBA where you can store a collection of data together with a unique key to each datapoint. The trick is to set the key the same as the data you are storing, when you try to add a key that is already held in the collection, it will give you an error, which yo can trap, and the data doesn't get adde. This way you get the unique list, without duplicates. OK, I probably complety lost you so let's do a small example first:

in a sheet:

Book1

A

1

mon

2

sat

3

sun

4

mon

5

tue

6

thu

7

fri

8

sat

9

tue

10

wed

11

thu

12

fri

13

wed

14

sun

Sheet4

Now add this code to a module:

Option Explicit Sub CollectionDemo() Dim colC As Collection Dim lR As Long Dim rC As Range Dim v As Variant ' Initialise the collection Set colC = New Collection ' load the data in the collection _ We trap the error to avoid the _ program halting. We can safely go to _ the next line. On Error Resume Next For Each rC In Range("A1:A14") ' add each cell to the collection as value and as key colC.Add Item:=rC.Value, Key:=rC.Value Next rC ' set error behaviour back to normal On Error GoTo 0 MsgBox "Collection colC has: " & colC.Count & " items." ' now output each item in the collection to column B Set rC = Range("B1") For Each v In colC rC = v Set rC = rC.Offset(1, 0) Next v 'clean up Set colC = Nothing Set rC = Nothing End Sub 

Now run the code.
It will first tell you that there are 7 items in the collection, then it will list these in column B.

In my next post I will show how to use this for your issue

blood_Milk_sky · May 2, 2017

Thank you ever so much I really appreciate your help

sijpie · May 2, 2017

OK, you want to filter your pivo table using each of the ID's held in a column, but you don't (obviously) want to use duplicate ID's.

So with the above knowledge of Collections you can read the whole list into a collection and so filter out any duplicates.
Next you can go through each item in the collection and create your filter from that.
Because I don't know the size of your table, it could be huge, I don't want to step through each cell of the column holding the ID's. Reading from (and writing to) the spreadsheet is slow. So we read the complete column into an array and then read the data from the array. Much faster.

Read the comments in the code. Adjust sheet names and cell address as instructed.

Notice that I replace ActiveSheet with a worksheet object wsPivot set to the sheet holding the pivot table. That way the macro will run properly regardless of which sheet is visible at the time. Let me know how it goes

'-----------------------------------------------------
Option Explicit Sub FilterOnItems() Dim colC As Collection Dim lR As Long, lC As Long Dim rC As Range, rID As Range Dim vDat As Variant, vID As Variant Dim wsData As Worksheet, wsPivot As Worksheet ' Initialise the collection Set colC = New Collection ' set the wsData to the worksheet holding the data table Set wsData = Sheets("Datasheet") ' <<< modify sheet name to suit ' set the wsPivot to the worksheet holding the Pivot table Set wsData = Sheets("Pivot") ' <<< modify sheet name to suit ' set rID to the first cel of the ID column, below the header Set rID = wsData.Range("B2") ' <<< modify cell to suit ' get the number of rows in the database lR = rID.CurrentRegion.Rows.Count - rID.Row + 1 ' read the column into an array. One read, so fast. vDat = rID.Resize(lR, 1).Value ' load the data in the collection _ We trap the error to avoid the _ program halting. We can safely go to _ the next line. On Error Resume Next For lC = 1 To UBound(vDat, 1) ' add each ID to the collection as value and as key colC.Add Item:=vDat(lC, 1), Key:=vDat(lC, 1) Next lC ' set error behaviour back to normal On Error GoTo 0 'while testing the macro this will print out into the _ "Immediate Window" in the VBA editor Debug.Print "Collection colC has: " & colC.Count & " items." ' now filter the pivot table on each ID in the collection in turn For Each vID In colC With wsPivot.PivotTables("PivotTable1").PivotFields("Business" & Chr(10) & "Partner ID") .ClearAllFilters .CurrentPage = vID End With ' do whatever needs to be done with the output '..... Next vID 'clean up Set colC = Nothing Set wsData = Nothing Set wsPivot = Nothing Set rID = Nothing End Sub

SpillerBD · May 2, 2017

Why not use the "Show Report Filter Pages..." ?

blood_Milk_sky · May 4, 2017

How do you mean? sorry super new to all of this

sijpie · May 4, 2017

Does the macro do the trick?

Your question

How do you mean?

, to which post does it refer?

blood_Milk_sky · May 4, 2017

Hi Sijpie,

I am just trying it now ^_^ Fingers crossed

If I have anytrouble with it can I show you the workbook?

blood_Milk_sky · May 4, 2017

Hi Sijpie,

I am trying the Macro but for some reason it is not working

SpillerBD · May 4, 2017

SpillerBD said:
Why not use the "Show Report Filter Pages..." ?

When in a Pivot Table that uses items in the Page Filter, go through the following ribbon path.
Pivottable tools/Analyze/Pivot Table/Options
select "Show Report Filter Pages"

This will create new worksheets for each available value in the selected filter item.
I use this feature to provide reports to 100's of individuals. I do have to select all the sheets and Copy/Paste as values to obliterate the Pivot Table so that the receivers cannot see the data for other locations.

Automating Pivot Table Filter Selection

blood_Milk_sky

New Member

Excel Facts

sijpie

Well-known Member

blood_Milk_sky

New Member

sijpie

Well-known Member

SpillerBD

Well-known Member

blood_Milk_sky

New Member

sijpie

Well-known Member

blood_Milk_sky

New Member

blood_Milk_sky

New Member

SpillerBD

Well-known Member

Similar threads

Forum statistics

Share this page

Automating Pivot Table Filter Selection

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock