VBA: How do I use/fix pivot item and pivot field?

[sjl]

I found some VBA code to separate a detailed list based on a pivot table filter. I modified the code from creating a separate file for each pivot item to creating a separate tab. That part works great.

The detailed list is tasks by employee and the separated filter is director.

The detailed data does a lookup from employee to director. The pivot table counts the number of employee tasks per director, based on task status.

The pivot table lives in E1:F5, where E1 is a filter of multiple items (active, pending or staged tasks); E2 is a filter of one director name and the count is in E5. The VBA goes through each pivot item and prints the data to a separate tab. The tab is named PivotTable, which I just realized I should change (I inherited the worksheet!). The table is named TableForBreakOut.

Note, in the original VBA model, there was only one filter and no multi filter.

Here;s the problem: Every now and then there is an employee who does not exist in the lookup. I see #NA in the details table, and I add them to the employee lookup table, refresh everything and then all the formulas and pivot tables are fine.

But the #NA from the lookup gets stuck in the pivot table variable somehow (found when hovering over "pvtItem" in the VBA window - I run it using F8 so I can see the line-by-line working)

I tried adding a clear variable line - but I have no idea what I am looking at, how to use them, etc. I am a VBA novice...

Here's my code:

Sub PrintSeparateDirectors()

Dim PvtTable As PivotTable
Dim strfield As PivotField
Dim PvtItem As PivotItem

' set variables of pivot table to break out
    Const strFieldName = "Director"
    Const strTriggerRange = "E5"

' go to table for navigation
    Sheets("PivotTable").Select
   
' Set the pivot table name
    Set PvtTable = ActiveSheet.PivotTables("TableForBreakOut")
    
' These three lines are what I am monkeying with - not in the original code
    'Set strfield = PvtTable.PivotFields(strFieldName)
    'Set PvtItem = strfield.PivotItems(0)
    ActiveSheet.PivotTables("TableForBreakOut").PivotCache.Refresh

' Start looping through each item in the selected field
    For Each PvtItem In PvtTable.PivotFields(strFieldName).PivotItems
        PvtTable.PivotFields(strFieldName).CurrentPage = PvtItem.Name  '<======  This is where it breaks.  I get a runtime error '5' Invalid procedure call or argument
        Range(strTriggerRange).ShowDetail = True
       
    ' Name the temp sheet for easy cleanup later
        ActiveSheet.Name = "TempSheet"
        
    ' copy data to new tab
        ActiveSheet.Cells.Copy
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Paste
        Cells.EntireColumn.AutoFit

    '  rename new tab to director name
        ActiveSheet.Name = PvtItem.Name
        Range("A1").Select

    ' delete the temp sheet
        Application.DisplayAlerts = False
        Sheets("Tempsheet").Delete
        Application.DisplayAlerts = True
          
    Next PvtItem

End Sub

It worked until that #NA showed up...

Help please!
Thanks!

 

[sjl]

The solution was a setting in the pivot table options -
"Retain items deleted from the data source" *None*