mikes6058
asked on
control 2 pivot tables selection based on cell value
The VBA code below will allow me to enter a value into cell H6 which will automatically change my pivot table selection. I would now like to add to the code so H6 will also change the selection of a second pivot table. The second pivot table will be exactly the same as the first but will obviously be named PivotTable2.
I'm not sure how to edit the code. Could some one be so kind as to edit and re-paste?
Private Sub Worksheet_SelectionChange( ByVal Target As Range)
'This line stops the worksheet updating on every change, it only updates when cell
'H6 or H7 is touched
If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub
'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
'Here you amend to suit your data
Set pt = Worksheets("Sheet1").Pivot Tables("Pi votTable1" )
Set Field = pt.PivotFields("Category")
NewCat = Worksheets("Sheet1").Range ("H6").Val ue
'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
End Sub
Thanks Mike
I'm not sure how to edit the code. Could some one be so kind as to edit and re-paste?
Private Sub Worksheet_SelectionChange(
'This line stops the worksheet updating on every change, it only updates when cell
'H6 or H7 is touched
If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub
'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
'Here you amend to suit your data
Set pt = Worksheets("Sheet1").Pivot
Set Field = pt.PivotFields("Category")
NewCat = Worksheets("Sheet1").Range
'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
End Sub
Thanks Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER