PivotTables with Multiple Fields

[Geoff Lilley]

Hey gang, I got a fun one.

I have a PivotTable with two page fields.

***The data that I'm describing is dummied up, but is illustrative of
the principle.***

In the data sheet that underlies the PivotTable, I have "City" in
column A, and "State" in Column B. I have a lookup table on another
sheet, so that to find the state associated with the city, it'd be:
=VLOOKUP(A2,stateName,2,false). So if "Sacramento" was in A2, then
"California" would be in B2. And so forth. I have "Date" in Column C,
and "Dollar Amount" in Column D.

My PivotTable has Date for the row field (grouped by month), Dollar
Amount in the data field, and then two page fields: first, the State,
then the City.

You can guess where this is going.

I want to get to the point where when the state page field changes, the
City page field shows only the cities associated with that state.

I modified some code from PivotTable legend Debra Dalgleish; it's kinda
working, but I wanted to get some help on maybe improving it. In
essence, I created a named range for each state. In B2, I created a
drop-down validation, using the named range "allStates" as the initial
source, and I put the Worksheet_Calculate code in the sheet containing
the PivotTable:

Private Sub Worksheet_Calculate()
'B2 is where the
Range("B2").Select
Select Case ActiveCell.Offset(-1, 0).Value
Case "California"
With Selection.Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
Formula1:="=californiaCities"
.InCellDropdown = True
End With
Case "Nevada"
With Selection.Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
Formula1:="=nevadaCities"
.InCellDropdown = True
End With
Case Else
With Selection.Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
Formula1:="=allStates"
.InCellDropdown = True
End With
End Select
End Sub

What I'm really trying to avoid is having to have 50 named ranges, one
for each state. I'll do it if I have to, but I'm really trying to
avoid that; seems like it should be easier than that.

Any suggestions would be greatly appreciated.

Thanks!

Cheers
Geoff