I have six data validation lists listed in hierarchy order. Each list depends on the selection made on the list that precedes it (E.G. Country, State, City, etc.)

I have a pivot table with Fields that relate to the values in the data validation lists, and I wish to alter it based on the selections I make on my lists. Specifically, I want to make my selections THEN click a button that will change my pivot table accordingly.

I have included relevant code below. So far, my pivot table changes based on my first data validation list but only because it's listed as a "Report Filter" in the Pivot Table Field List. I want ALL of my fields to be row labels.


Private Sub Worksheet_Change(ByVal TargetGroup As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strGroup As String
Dim strArea As String
Dim strUnit As String

strGroup = "GROUP"
strArea = "AREA"
strUnit = "UNIT"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

If TargetGroup.Address = Range("SelGroup").Address Then
'With ActiveSheet.PivotTables("PivotTable1").PivotFields("GROUP")
' For Each ws In ThisWorkbook.Worksheets
Set ws = Me
With ActiveSheet.PivotTables("PivotTable1").PivotFields("GROUP")
For Each pi In .PivotItems
If pi.Value = TargetGroup.Value Then
.CurrentPage = TargetGroup.Value
Exit For
.CurrentPage = "(All)"
End If
Next pi
End With
' Next ws

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


Thank you in advance for any help!!