Items in Report Filter of PivotTable not "checked" automatically

S

shabutt

I have two pivottables connected to a master dataset. These PT (on separate
sheets) and dataset are in separate books. I have set different report
filters but on common field for each PT. The 1st PT shows all items except
blank and the 2nd PT shows only blank.

My problem lies in 1st PT. Whenever master dataset changes, it reflects in
PT but the updated field data is not automatically checked in report filter
list and I have to manually select it to show updated data in PT.

Please help me out.
 
S

smartin

shabutt said:
I have two pivottables connected to a master dataset. These PT (on separate
sheets) and dataset are in separate books. I have set different report
filters but on common field for each PT. The 1st PT shows all items except
blank and the 2nd PT shows only blank.

My problem lies in 1st PT. Whenever master dataset changes, it reflects in
PT but the updated field data is not automatically checked in report filter
list and I have to manually select it to show updated data in PT.

Please help me out.

Not sure there is any way around it. That behavior makes sense though
since PT1's filter cannot know you want "everything except blank" when
new categories appear, whereas in PT2 "blank" is always in the data.
 
S

shabutt

After a lot of search on web I came across this code on
"www.dailydoseofexcel.com" and modified for my situation.

Sub Auto_Open()

Application.ScreenUpdating = False
With Worksheets("Paid").PivotTables("PivotTable1").PivotFields("CPR NO#")
For x = 1 To .PivotItems.Count
..PivotItems(x).Visible = True
Next
..PivotItems("(blank)").Visible = False
End With
Application.ScreenUpdating = True
End Sub

When I open my PTs file for first time there are no check marks on recently
added pivot items but the second time I open the file, all the pivot items
except the blank pivot item have check marks on them which is what I require.
I don't know what is causing this. In the connections property of PTs I have
set "Refresh data when opening the file". I am using Excel 2007 and limited
vba knowledge.

Please let me know if anybody has got any clue.

Regards.
 
S

smartin

Good find! I wonder if the Auto_Open code fires before the PT refreshes
itself.

What you could try is inserting a command to force the PT to refresh first:

Application.ScreenUpdating = False
With Worksheets("Paid")
..PivotTables("PivotTable1").PivotCache.Refresh
..PivotTables("PivotTable1").PivotFields("CPR NO#")
For x = 1 To .PivotItems.Count
..PivotItems(x).Visible = True
Next
..PivotItems("(blank)").Visible = False
End With
Application.ScreenUpdating = True
End Sub
 
S

smartin

Whoops! I broke the context of the With statement. Try this instead:

Application.ScreenUpdating = False
Worksheets("Paid").PivotTables("PivotTable1").PivotCache.Refresh
With Worksheets("Paid").PivotTables("PivotTable1").PivotFields("CPR NO#")
For x = 1 To .PivotItems.Count
..PivotItems(x).Visible = True
Next
..PivotItems("(blank)").Visible = False
End With
Application.ScreenUpdating = True
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top