insaneoctane
Board Regular
- Joined
- Dec 2, 2005
- Messages
- 72
I keep getting handed pivot tables to work with- sometimes regular, sometimes OLAP. One thing in common is they always have a column of data that I need to filter with the same values. I had an idea to write a VBA macro in my personal.xlsb file so it would always be accessible to me as I open yet another pivot table file and need to filter the data. I wanted to put the cursor (activecell) in the pivot field that contains my filter list since the name of the pivotfield would always be different from table to table. I wanted to use the activecell to identify the pivotfield in question and then filter from my pre-determined list (see FilterArray below). I wrote it, initially for regular pivot data below, but then learned on my first try that I was also receiving OLAP data and it didn't work. Can I get some help on making the below macro work for either standard or OLAP as described?
FWIW- My actual FilterArray will actually contain 10 items (lots of clicks when doing manually) and the selection to choose from has thousands of options (making finding them difficult), hence why I'm looking to automate!
VBA Code:
Sub Pivot_filterCC()
Dim PT As PivotTable
FilterArray = Array("42633", "42614", "42612")
Dim myPivotField As PivotField
Set myPivotField = ActiveCell.PivotField
myPivotField.ClearAllFilters
myPivotField.EnableMultiplePageItems = True
numberOfElements = UBound(FilterArray) - LBound(FilterArray) + 1
If numberOfElements > 0 Then
With myPivotField
For i = 1 To myPivotField.PivotItems.Count
j = 0
Do While j < numberOfElements
If myPivotField.PivotItems(i).Name = FilterArray(j) Then
myPivotField.PivotItems(myPivotField.PivotItems(i).Name).Visible = True
Exit Do
Else
myPivotField.PivotItems(myPivotField.PivotItems(i).Name).Visible = False
End If
j = j + 1
Loop
Next i
End With
End If
End Sub
FWIW- My actual FilterArray will actually contain 10 items (lots of clicks when doing manually) and the selection to choose from has thousands of options (making finding them difficult), hence why I'm looking to automate!