werafa
03-13-2014, 11:03 PM
Hi all,
I have 4 pivot tables that connect to an external data source - and I am trying to use VBA to preset the pivot filters.
....
and failing
....
The following code generates an "unable to set page property of pivotfield class" error
Sub FilterPivots()
Dim qcode As String
Dim pT As PivotTable
Dim wB As Workbook
Dim wS As Worksheet
Dim myString As String
Set wB = ActiveWorkbook
'Call RunFast
qcode = Worksheets("Instructions").Range("C7").Value
myString = "[Facility].[Quick Code].&[" & qcode & "]"
If qcode = "AAN" Then qcode = GetQCode
For Each wS In wB.Worksheets
For Each pT In wS.PivotTables
'On Error Resume Next
pT.PivotFields("[Facility].[Quick Code].[Quick Code]").ClearAllFilters
pT.PivotFields("[Facility].[Quick Code].[Quick Code]").CurrentPage = "[Facility].[Quick Code].&[MJP]" ' string is as recorded by macro recorder - macro fails at this point
'original recording showed activesheet.pivottables(".......... .Pivotfields etc.
'pT.PivotFields("[Facility].[Quick Code].[Quick Code]").CurrentPage = mystring
'On Error GoTo 0
Next pT
Next wS
Call ResetApp
Set wB = Nothing
Set wS = Nothing
Set pT = Nothing
End Sub
The goal is to loop through all pivot tables in workbook and set the 'quick code' field - which is set in the page filters section of the table
This code is a mash plagiarised from the best(?) of the web (the looping bit - this works) and the macro recorder (clear filters works, but set filters does not)
I run win7, Office 2013 - 64 bit, and use slicers in the pivot tables.
Does anyone know where I am getting lost?
Thanks
Tim
I have 4 pivot tables that connect to an external data source - and I am trying to use VBA to preset the pivot filters.
....
and failing
....
The following code generates an "unable to set page property of pivotfield class" error
Sub FilterPivots()
Dim qcode As String
Dim pT As PivotTable
Dim wB As Workbook
Dim wS As Worksheet
Dim myString As String
Set wB = ActiveWorkbook
'Call RunFast
qcode = Worksheets("Instructions").Range("C7").Value
myString = "[Facility].[Quick Code].&[" & qcode & "]"
If qcode = "AAN" Then qcode = GetQCode
For Each wS In wB.Worksheets
For Each pT In wS.PivotTables
'On Error Resume Next
pT.PivotFields("[Facility].[Quick Code].[Quick Code]").ClearAllFilters
pT.PivotFields("[Facility].[Quick Code].[Quick Code]").CurrentPage = "[Facility].[Quick Code].&[MJP]" ' string is as recorded by macro recorder - macro fails at this point
'original recording showed activesheet.pivottables(".......... .Pivotfields etc.
'pT.PivotFields("[Facility].[Quick Code].[Quick Code]").CurrentPage = mystring
'On Error GoTo 0
Next pT
Next wS
Call ResetApp
Set wB = Nothing
Set wS = Nothing
Set pT = Nothing
End Sub
The goal is to loop through all pivot tables in workbook and set the 'quick code' field - which is set in the page filters section of the table
This code is a mash plagiarised from the best(?) of the web (the looping bit - this works) and the macro recorder (clear filters works, but set filters does not)
I run win7, Office 2013 - 64 bit, and use slicers in the pivot tables.
Does anyone know where I am getting lost?
Thanks
Tim