G
Guest
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...
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...