Hi!
Very smart and nifty, and works like a charm too!
Thank You.
(BTW the field I was specifically targetting was indeed called "Rep"!)
Brgds
Danny
"Debra Dalgleish" wrote in message
...
Good point about the (blanks) -- I forgot about those. You could remove
and replace the field from the page area:
'==========================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsPT As Worksheet
Set wsPT = Worksheets("Pivot")
If Target.Count 1 Then Exit Sub
If Target.Address = "$H$2" Then
If Target.Value = "" Then
wsPT.PivotTables(1) _
.PivotFields("Rep").Orientation = xlHidden
With wsPT.PivotTables(1) _
.PivotFields("Rep")
.Orientation = xlPageField
.Position = 1
End With
Else
wsPT.PivotTables(1).PivotFields("Rep") _
.CurrentPage = Target.Value
End If
End If
End Sub
'============================
Daniel Magnus Bennét Björck wrote:
Hi!
Thank you, but unfortunately I want to do it the other way around.
If one particular cell is left blank, I want the pivottable to switch to
the "(all)" page. How do I do that?
(BTW, your example would select "(blanks)" as well)
Brgds
Danny
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html