Hi,

I've got a workbook that is connected to an external dataset. The
workbook is just a pivot table that pulls data from the external
dataset. It shows sales counts for a specified product for all
countries worldwide for all or selected years. The pivot table shows
all countries vertically in column B, and the years horizontally in
row 4, and of course with the data counts in the adjacent cells.

Users of the pivot table regularly need to filter the countries down
to a specific subset of 33 countries. Not all of those 33 countries
are in the returned data, but they have to look through the entire
list of ALL countries returned and manually select those in the subset
that are there. This is obviously tedious and error prone.

I've tried to adapt a VBA solution I found to filter the list, but I
keep getting an error message saying 'Unable to get the PivotFields
property of the PivotTable class'. I'm wondering if that's because
the pivot table is pulling from an external dataset connection. That
code is at the bottom.

I also tried a Slicer with a VBA solution to automatically filter the
Slicer. Below is a small part of the code for applying that subset of
countries filter. It works, so long as the country is actually in the
data the pivot table returns for the given product that is being
looked at. If the product isn't sold in that country, say Belgium for
instance, the macro bugs out...because it can't filter on something
that isn't there.

Any thoughts on what might be wrong with the filter code at the
bottom, or alternately, how I might be able to adjust the Slicer code
to bypass an item in the array if it's not present in the pivot table
country list?

Thanks!!

Slicer Code:
Sub SetEUcountries()
.VisibleSlicerItemsList = Array( _
"[Financial Org].[Top Countries].[Country Name].&[Other
OUS]&[AUSTRIA]", _
"[Financial Org].[Top Countries].[Country Name].&[Other
OUS]&[Belgium]", _
"[Financial Org].[Top Countries].[Country Name].&[OUS]&[UNITED
KINGDOM]")
End Sub

Pivot table filter code:
Private Function Filter_PivotField(pvtField As PivotField, _
varItemList As Variant)
Dim strItem1 As String
Dim i As Long
On Error GoTo ErrorHandler:
Application.ScreenUpdating = False

strItem1 = varItemList(LBound(varItemList))
With pvtField
.PivotItems(strItem1).Visible = True
For i = 1 To .PivotItems.Count
If .PivotItems(i) < strItem1 And _
.PivotItems(i).Visible = True Then
.PivotItems(i).Visible = False
End If
Next i
For i = LBound(varItemList) + 1 To UBound(varItemList)
.PivotItems(varItemList(i)).Visible = True
Next i
End With
Exit Function
ErrorHandler:
MsgBox "Error while trying to process item: " & varItemList(i)
End Function

Sub Filter_ItemListInRange()
Filter_PivotField _
pvtField:=Sheets("Sales
Figs").PivotTables("PivotTable1").PivotFields("Cou ntry"), _
varItemList:=Application.Transpose(Sheets("EU Countries Unit
Sold In").Range("EUcountries"))

End Sub

Sub test()
Dim PI As PivotItem
With Worksheets("Sales
Figs").PivotTables("PivotTable1").PivotFields("Cou ntry")
.ClearAllFilters
For Each PI In .PivotItems
PI.Visible =
WorksheetFunction.CountIf(Range("EUcountries"), PI.Name) 0
Next PI
End With
End Sub