Loop through pivot table - VBA

[DutchBaron]

Hi guys,

I've got a problem.

I'm working with a database and periodic reports that need to be run. I've set up a pivot table that can be updated monthly with departments in column labels and order data on row labels. What I'm looking to achieve is generating output specific to users. That means: a single excel file tailored to a department.

What I've gotten so far:

Sub PivotLoop()


Dim PvTable As PivotTable
Dim PvField As PivotField
Dim PvItem As PivotItem
Dim PvItemL As PivotItem
Set PvTable = ActiveSheet.PivotTables("PivotTable2")
Set PvField = PvTable.PivotFields("Department")




For Each PvItem In PvField.PivotItems
    For Each PvItemL In PvField.PivotItems
        If PvItemL = PvItem Then
            PvField.PivotItems(PvItemL).Visible = True 'Error occurs here
            Else: PvField.PivotItems(PvItemL).Visible = False
        End If

    Next
    ' code to save file goes here
Next
End Sub

However, this loop yields an error: Run-time error '1004': unable to get the PivotItems property of the PivotField class. Can you guys help me adjusting this code?
Thanks for all input!

 

[nuked]

Instead of:

 PvField.PivotItems(PvItemL).Visible = True

try

 PvItemL.Visible = True

 

[DutchBaron]

Instead of:

 PvField.PivotItems(PvItemL).Visible = True

try

 PvItemL.Visible = True

This works, thank you!
The next problem that I encounter is that for the 1st loop on the 2nd iteration all items are hidden so again I see an error. Is there a quick way to show all items?

 

[DutchBaron]

Sorry, ignore my previous post. I think the problem is that in the 2nd iteration a phase occurs where no value is selected, which cannot hold for a pivot table. Adding "on error resume next" results in the procedure skipping the 'last' value that would have been set as invisible and the logical result is that 2 values are shown in the table, which i don't want. Any and all suggestions?

 

[s hal]

If you know what is causing the error, couldn't you add a test for that specific condition and do something else in your loop?

 

[par60056]

Before:
For Each PvItemL

add the line:
PvField.PivotItems(PvField.PivotItems.Count-1).Visible=True

This will turn on the last item so that you don't have a case where all items have Visible set False.

 

[DutchBaron]

With slight adjustment Par60056's suggestion works, thanks!

 

[TCookie]

With slight adjustment Par60056's suggestion works, thanks!

Hi there, could you tell me what adjustment you made please. I'm trying to do the same thing, but it comes up with an error. Thank in advance.