I have a sheet with some 'normal' pivots, as well as a couple of data models (for the distinct count feature).
I have some code which loops through the pivot items and selects the one that matches my string. This works fine for the normal pivots, but I can't get it to work for the data model ones - it throws up an error of 'Object doesn't support this property or method'. Any help/suggestions welcome!
Dim pvtTables As PivotTables Dim pvtTable As PivotTable Dim z as String z = Import '<<< 'Import is my variable text Dim pivForce As PivotItem With Sheets("KPI's") .Activate .PivotTables("PivotTable1").PivotFields("cs_jurisdiction"). _ ClearAllFilters For Each pivForce In Worksheets("KPI's").PivotTables("PivotTable1").PivotFields("cs_jurisdiction").PivotItems If pivForce.Value = Import Then .PivotTables("PivotTable1").PivotFields("cs_jurisdiction"). _ CurrentPage = Import Exit For Else: CurrentPage = "(blank)" End If Next 'above code works fine .PivotTables("PivotTable3").PivotFields("[Range].[Force].[Force]"). _ ClearAllFilters For Each pivForce In Worksheets("KPI's").PivotTables("PivotTable3").PivotFields("[Range].[Force].[Force]") '<<< error appears on this line If pivForce.Value = Import Then .PivotTables("PivotTable3").PivotFields("[Range].[Force].[Force]"). _ CurrentPage = "[Range].[Force].&[" & i & "]" Exit For Else: CurrentPage = "[Range].[Force].&[""(blank)""]" End If Next
Bookmarks