Change filter in Pivot table based on reference cell help

[PCRIDE]

Hi, I am following this code and works perfect, however I need to update 3 pivot tables on 3 different tabs.

http://dedicatedexcel.com/how-to-control-excel-pivot-tables-from-a-cell-value-with-vba/

I just repeated each line 3x, assigned new variables and it worked, but there should be a better way?




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("F12:F13")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt1 As PivotTable
Dim pt2 As PivotTable
Dim pt3 As PivotTable

Dim Field1 As PivotField
Dim Field2 As PivotField
Dim Field3 As PivotField

Dim NewCat1 As String
Dim NewCat2 As String
Dim NewCat3 As String


'Here you amend to suit your data
Set pt1 = Worksheets("Monthly").PivotTables("PivotTable2")
Set pt2 = Worksheets("Weekly").PivotTables("PivotTable4")
Set pt3 = Worksheets("Daily").PivotTables("PivotTable1")
Set Field1 = pt1.PivotFields("Core")
Set Field2 = pt2.PivotFields("Core")
Set Field3 = pt3.PivotFields("Core")
NewCat1 = Worksheets("Dashboard").Range("F12").Value
NewCat2 = Worksheets("Dashboard").Range("F12").Value
NewCat3 = Worksheets("Dashboard").Range("F12").Value

'This updates and refreshes the PIVOT table
With pt1
Field1.ClearAllFilters
Field1.CurrentPage = NewCat1
'pt1.RefreshTable
End With
With pt2
Field2.ClearAllFilters
Field2.CurrentPage = NewCat2
'pt2.RefreshTable
End With
With pt2
Field3.ClearAllFilters
Field3.CurrentPage = NewCat3
'pt3.RefreshTable
ActiveWorkbook.RefreshAll
End With


End Sub

 

[PCRIDE]

Just wanted to see if anyone has an idea.

the code works as is, but can be simpler. I did try just assigning the one variable , in this case let's say PT

Dim pt As PivotTable

Set pt = Worksheets("Sheet1).PivotTables("PivotTable1")
Set pt = Worksheets("Sheet1).PivotTables("PivotTable2")
Set pt = Worksheets("Sheet1).PivotTables("PivotTable3")
Set pt = Worksheets("Sheet2).PivotTables("PivotTable4")
Set pt = Worksheets("Sheet2).PivotTables("PivotTable5")

Wouldn't this work? Calling multiple pivot tables with one variable? How can I change from calling a specific sheet, to all pivot tables in the workbook?

 

[PCRIDE]

Does anyone know if this code works on a pivot table connected to a datasource? Datacube to be precise.

I get an error that it can't find the pivotTable

 

[PCRIDE]

I think the problem is when I activate the filter in the pivot table manually, it begins to query the tables via OLAP data cube and for some reason this code doesn't see the pivot table as a local pivot table. Any idea?

Error
Unable to get the pivotFields property of the PivotTable Class, runtime 1004.

THe debugger highlights this line, which is the filter of the pivotTable

Set Field = pt.PivotFields("Sales")

 

[PCRIDE]

This seems to be the issue I'm having.

https://social.msdn.microsoft.com/F...ap-pivot-table-based-on-a-cell?forum=exceldev

 

[PCRIDE]

I getting a bit closer.

'This line stops the worksheet updating on every change, it only updates when cell
'H6 or H7 is touched
If Intersect(Target, Range("D3:D3")) 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("DataCalc").PivotTables("PivotTable10")
Set Field = pt.PivotFields("[Sales].[TSales.[Sales]")
NewCat = Worksheets("Dashboard").Range("D2").Value

'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub


Its now failing on Clearing the filters i.e Field.ClearAllFilters code. Since I referrenced the data cube field by using [Sales].[TSales.[Sales], I need to do the same for the Field.ClearAllFilters

How do I do this?

 

[PCRIDE]

Ok so apparently this cannot be done using OLAP /DataCubes connections via Pivot Table. So the work around is to use multiple slicers connected to multiple pivot tables.

One pivot table is the main data source (My dashboard connects to the pivot table since the data is dynamic
The other Pivot table (copy of the main) feeds drop down selections
Slicers are connected to both to change them
The Dashboard formula's take over and create the views.

Pretty awesome I must say.