Hello,
This is really amazing, I was working with creating a dash board with a lot of pivot tables, and in some point I discovered that all the tables had the origin from an external file, in order to chage the origin I used this code (that I found on Internet):
Option Explicit
Sub Cambiar_Origen_Tbls_Dinamicas()
Dim n As Integer
Dim i As Integer
Dim pt As PivotTable
n = ActiveWorkbook.Worksheets.Count
For i = 1 To n
For Each pt In ActiveWorkbook.Worksheets(i).PivotTables
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:="NuevoOrigen")
Next pt
Next i
End Sub
Nevertheless to used it I had to disconect all the slicers and actually it work, nevertheless at the moment of created or conect new slicers they only reconized one pivot table and if I use the last code apperars error 1004, can you help me please?.
Hi Cristian,
Without your workbook it's difficult to understand exactly what the issue is, or test what is happening. Please, always attach a workbook.
Every time this line runs
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create (SourceType:=xlDatabase, SourceData:="NuevoOrigen")
you create a new pivot cache.
You can only attach slicers to pivot tables that share a cache. As this code creates a new cache for each pivot table, your slicers will only be able to connect to one pivot table.
One solution is to manually create a pivot table from the new source which is in your workbook, then replace the line above with this
pt.CacheIndex = Sheets("NewPivot").PivotTables(1).CacheIndex
which attaches the pivot tables to the same cache.
Regards
Phil
