Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Items in Report Filter of PivotTable not "checked" automatically
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Items in Report Filter of PivotTable not "checked" automatically
shabutt wrote:
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Items in Report Filter of PivotTable not "checked" automatical
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").Pivo tFields("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. "smartin" wrote: shabutt wrote: 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. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Items in Report Filter of PivotTable not "checked" automatical
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 shabutt wrote: 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").Pivo tFields("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. "smartin" wrote: shabutt wrote: 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. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Items in Report Filter of PivotTable not "checked" automatical
Whoops! I broke the context of the With statement. Try this instead:
Application.ScreenUpdating = False Worksheets("Paid").PivotTables("PivotTable1").Pivo tCache.Refresh With Worksheets("Paid").PivotTables("PivotTable1").Pivo tFields("CPR NO#") For x = 1 To .PivotItems.Count ..PivotItems(x).Visible = True Next ..PivotItems("(blank)").Visible = False End With Application.ScreenUpdating = True End Sub smartin wrote: 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: </code zapped shabutt wrote: 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").Pivo tFields("CPR NO#") For x = 1 To .PivotItems.Count .PivotItems(x).Visible = True Next .PivotItems("(blank)").Visible = False End With Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter PivotTable dropdown items to match report filter | Excel Discussion (Misc queries) | |||
How to keep "Header Row" button checked during Sort | Excel Discussion (Misc queries) | |||
How do I move a document from "recent items" to "documents" | New Users to Excel | |||
when a "check box" is checked, a "result" to be shown in another c | Excel Discussion (Misc queries) | |||
Can I keep "Windows of active workbook check box" checked | Excel Discussion (Misc queries) |