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

sjl

New Member
Joined
May 30, 2007
Messages
30
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:

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!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
The solution was a setting in the pivot table options -
"Retain items deleted from the data source" *None*
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top