Dear all,
I'm using an excel workbook with several pivot tables in it.
As the work is continuously updating, I needed a piece of code that allowed me to update all the data source of all the pivot tables in just one click, so I used this piece of code.
At the end of the job, I need to create several Slicers, to help users to navigate through the pivot tables.
Unfortunately, when I create the slicers, there's no way to connect them to multiple pivot tables.
The range is exactly the same for all the pivot, the data the tables show are updated and coherent, but Slicers don't seem to "understand".
If, just to try, I manually go to option->change data source and click "OK" (without changing anything) on two different pivots, then I can link them with the slicer.
What am I missing? Is there something I can add to the vba code?
Thanks a lot in advance!!
Matteo
I'm using an excel workbook with several pivot tables in it.
As the work is continuously updating, I needed a piece of code that allowed me to update all the data source of all the pivot tables in just one click, so I used this piece of code.
Code:
Sub Change_Pivot_Source()
Dim pt As PivotTable
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
For Each pt In sht.PivotTables
sht.Activate
pt.ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Campaign report!R3C1:R12500C327")
pt.PivotCache.Refresh
Next pt
Next sht
ActiveWorkbook.RefreshAll
End Sub
At the end of the job, I need to create several Slicers, to help users to navigate through the pivot tables.
Unfortunately, when I create the slicers, there's no way to connect them to multiple pivot tables.
The range is exactly the same for all the pivot, the data the tables show are updated and coherent, but Slicers don't seem to "understand".
If, just to try, I manually go to option->change data source and click "OK" (without changing anything) on two different pivots, then I can link them with the slicer.
What am I missing? Is there something I can add to the vba code?
Thanks a lot in advance!!
Matteo