Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating through Page Fields of a Pivot Table
I wrote a useful routine which responds to a right-click of a data cell of a
pivot table. The routine behaves very similar to a double-click of a pivot cell, but instead of creating a new sheet with a copy of the underlying rows, it applies an autofilter to the data sheet source for the pivot table. Thus, after you right-click, you can make changes to the "live" version of the filtered rows (e.g., to reclassify a dimension), and see the changes immediately in the pivot table after a "refresh data". I find this extremely useful for "cleansing" large financial transaction spreadsheets. Here is the code: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) ' Note: The Pivot sheet must be named "Pivot" and the Data sheet must be named "Data" ' Note: Put this routine in the Pivot sheet Dim DS As Worksheet Dim i As Integer Dim PF As PivotField Application.ScreenUpdating = False 'Turn off screen updating Set DS = Sheets("Data") 'Get reference to Data sheet On Error GoTo ErrorExit 'For errors (eg., right click on non-Pivot cell) get out With Target.PivotCell 'Refer to pivot cell DS.Range("A1").AutoFilter 'Clear out previous autofilters For Each Item In .RowItems 'For each row dimension in the pivot... i = 1 While DS.Cells(1, i) < Item.Parent 'Iterate on the autofilter title row to find index of the dimension i = i + 1 Wend Call DS.Range("A1").AutoFilter(i, Item.Name) 'Found it, generate appropriate autofilter action Next Item For Each Item In .ColumnItems 'For each column dimension in the pivot i = 1 While DS.Cells(1, i) < Item.Parent 'Iterate on the autofilter title row to find index of the dimension i = i + 1 Wend Call DS.Range("A1").AutoFilter(i, Item.Name) 'Found it, generate appropriate autofilter action Next Item End With ErrorExit: 'Right-click on non-Pivot cell exit Application.ScreenUpdating = True 'Turn on screen updating again End Sub My problem is that I can't figure out how to factor in the page fields with selected values (i.e., the ones which have values selected). If you look at my code, you can see how it iterates through RowItems and ColumnItems, and uses them to apply the Autofilter. Unfortunately, there's no "PageItems" collection to do this (or at least I can't find it). Anybody have any idea how I can do this? Thanks in advance, TommyVee PS. The ability to double-click a pivot table cell and have it Autofilter on the base data (rather than create a new sheet) is a standard feature in Quattro. I wish it was part of Excel so I wouldn't have to write code like this to do it. Maybe some day... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating through Page Fields of a Pivot Table
There is a pagefields collection
-- Regards, Tom Ogilvy "TommyVee" wrote in message ... I wrote a useful routine which responds to a right-click of a data cell of a pivot table. The routine behaves very similar to a double-click of a pivot cell, but instead of creating a new sheet with a copy of the underlying rows, it applies an autofilter to the data sheet source for the pivot table. Thus, after you right-click, you can make changes to the "live" version of the filtered rows (e.g., to reclassify a dimension), and see the changes immediately in the pivot table after a "refresh data". I find this extremely useful for "cleansing" large financial transaction spreadsheets. Here is the code: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) ' Note: The Pivot sheet must be named "Pivot" and the Data sheet must be named "Data" ' Note: Put this routine in the Pivot sheet Dim DS As Worksheet Dim i As Integer Dim PF As PivotField Application.ScreenUpdating = False 'Turn off screen updating Set DS = Sheets("Data") 'Get reference to Data sheet On Error GoTo ErrorExit 'For errors (eg., right click on non-Pivot cell) get out With Target.PivotCell 'Refer to pivot cell DS.Range("A1").AutoFilter 'Clear out previous autofilters For Each Item In .RowItems 'For each row dimension in the pivot... i = 1 While DS.Cells(1, i) < Item.Parent 'Iterate on the autofilter title row to find index of the dimension i = i + 1 Wend Call DS.Range("A1").AutoFilter(i, Item.Name) 'Found it, generate appropriate autofilter action Next Item For Each Item In .ColumnItems 'For each column dimension in the pivot i = 1 While DS.Cells(1, i) < Item.Parent 'Iterate on the autofilter title row to find index of the dimension i = i + 1 Wend Call DS.Range("A1").AutoFilter(i, Item.Name) 'Found it, generate appropriate autofilter action Next Item End With ErrorExit: 'Right-click on non-Pivot cell exit Application.ScreenUpdating = True 'Turn on screen updating again End Sub My problem is that I can't figure out how to factor in the page fields with selected values (i.e., the ones which have values selected). If you look at my code, you can see how it iterates through RowItems and ColumnItems, and uses them to apply the Autofilter. Unfortunately, there's no "PageItems" collection to do this (or at least I can't find it). Anybody have any idea how I can do this? Thanks in advance, TommyVee PS. The ability to double-click a pivot table cell and have it Autofilter on the base data (rather than create a new sheet) is a standard feature in Quattro. I wish it was part of Excel so I wouldn't have to write code like this to do it. Maybe some day... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - Show Page Fields | Excel Discussion (Misc queries) | |||
Pivot Table Page fields | Excel Discussion (Misc queries) | |||
Pivot Table page fields | Excel Discussion (Misc queries) | |||
How do I set up filter for page fields in pivot table? | Excel Discussion (Misc queries) | |||
Pivot Table Page Fields | Excel Discussion (Misc queries) |