VBA Filtering PivotField - Error Unable to get the objects property of the worksheet class

[MarieThompson]

I'm a newbie to VBA and trying to use VBA to filter PivotField name 'Zone' on a PivotTable, but I'm getting the 'run time error 1004: unable to get the objects property of the worksheet class' on line 'Set pf = pt.PivotFields("Zone")'. Things I already tried:

- Checked the PivotTable Name 'TrackingTable' and Field name 'Zone' on tab PivotTable Analyze and Field Settings, and it's correct
- Tried to check the field names by code

Sub CheckFields()
Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables("TrackingTable")

For Each pf In pt.PivotFields
Debug.Print ("TrackingTable")
Next
End Sub

- Tried to refresh pivot cache by code

Sub RefreshAllPivotCaches()

Dim wb As Workbook
Dim lPCs As Long
Dim lPC As Long
Dim lProb As Long

Set wb = Application.ThisWorkbook
lPCs = wb.PivotCaches.Count

For lPC = 1 To lPCs
wb.PivotCaches(lPC).Refresh
If Err.Number <> 0 Then
MsgBox "Could not refresh pivot cache " & lPC _
& vbCrLf _
& "Error: " _
& vbCrLf _
& Err.Description
Err.Clear
lProb = lProb + 1
End If
Next lPC

MsgBox "Refresh is complete. " _
& vbCrLf _
& "Pivot Cache Count: " & lPCs _
& vbCrLf _
& "Failed refreshes: " & lProb

End Sub

But it keeps giving me the same error. Could someone help me? It's a simple problem, but there's something I'm not seeing.

My code:

Sub FilterDA6()


Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Tracking")

Set pt = ws.PivotTables("TrackingTable")
Set pf = pt.PivotFields("Zone")

pf.ClearAllFilters

pf.PivotFilters.Add Type:=xlCaptionEquals, Value1:="DA6"

Set pt = Nothing
Set pf = Nothing
Set ws = Nothing

```

End Sub

Note: Im using Excel 2016

 

[Micron]

If that doesn't help, try referring to the index number of the pivot table instead, or put a break point on the problem line and run the code. When it stops, type
?pt.Name
in the immediate window and hit Enter. See if the name that is returned is spelled exactly as you think it should be. I've found that when creating code for some objects, the real name is not the same as what is used in the procedure name.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA Filtering PivotField - Error Unable to get the objects property of the worksheet class
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.