CODEWITHSUNDEEP
vbaexcelpivot-table
Gring
Gring

Reputation: 318

Change datasource for an existing pivot cache

I have about ten PivotTables, each one on another worksheet.

I need to change the data source for each one of them into the same value. Pivotcaches.count give me 1 for thisworkbook so I assume every Table points to this cache.

Have you some suggestions on how to change this cache and not create ten new ones?

Here is what I got :

Sub Test(Ziel As String, Zieltab As String)
    Dim RNG As Range
    Dim letzteZeile As Double
    Dim letzteSpalte As Double
    Dim Spalte As String
    Dim Index As Integer
    Dim Wb As Workbook
    Dim Ws As Worksheet
    Dim pC As PivotCache
    Dim pT As PivotTable


    letzteZeile = Workbooks(Ziel).Worksheets(Zieltab).UsedRange.SpecialCells(xlCellTypeLastCell).Row
    letzteSpalte = Workbooks(Ziel).Worksheets(Zieltab).Range("A1").SpecialCells(xlCellTypeLastCell).Column
    Spalte = Split(Workbooks(Ziel).Worksheets(Zieltab).Cells(1, letzteSpalte).Address, "$")(1)

    Set RNG = Worksheets(Zieltab).Range("A1:" & Spalte & letzteZeile)



    Set Wb = ActiveWorkbook
    Set pC = Wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'" & Wb.Sheets(Zieltab).Name & "'!" & RNG.Address, Version:=xlPivotTableVersion12)


    For Each Ws In Wb.Sheets
        For Each pT In Ws.PivotTables
            Set pT.PivotCache = pC
        Next pT
    Next Ws
End Sub

This is the actual look of it. It says the Method would not Match or sth like that.

Upvotes: 1

Views: 2690

Answers (2)

FCastro
FCastro

Reputation: 631

I agree with R3UK's answer, but I've experienced too much cross-version trouble (Excel 2013 to Excel 2010) using the Create method for both PivotTables and PivotCaches. Either the last argument, Version, changed its name or the Excel 2013 (Application.Version 15.0) PivotTable standard (xlPivotTableVersion15) when used is not recognized by Excel 2010 (Application.Version 14.0) (standard xlPivotTableVersion14), I suppose.

I find the Add method less risky. It can be used to create PivotTables easily in a similar manner.

Sub ChangeCacheWithAdd()
Dim wB As Workbook, _
    wS As Worksheet, _
    pC As PivotCache, _
    pT As PivotTable, _
    bCreated As Boolean

For Each wS In wB.Sheets
    For Each pT In wS.PivotTables
        If Not bCreated Then
            Set pC = wB.PivotCaches.Add(SourceType:=xlDatabase, _
                   SourceData:="'Sheet Name'!" & Range("Named_Range").Address
            pT.ChangePivotCache pC
            bCreated = True
        Else
            If pT.CacheIndex <> pC.Index Then pT.CacheIndex = pC.Index
        End If
    Next pT
Next wS

'Save to delete unused Pivot Caches
wB.Save
End Sub

Upvotes: 0

R3uK
R3uK

Reputation: 14537

As you have only one Pivot Cache, you need to create a new one and then apply it to your Pivot Tables.

Here the Pivot Cache is created from the Range named Named_Range in the Sheet named Sheet_Name, I leave it to you to rename these to fit your need! ;)

Sub Test_Gring()
Dim wB As Workbook, _
    wS As Worksheet, _
    pC As PivotCache, _
    pT As PivotTable, _
    bCreated As Boolean

For Each wS In wB.Sheets
    For Each pT In wS.PivotTables
        If Not bCreated Then
            pT.ChangePivotCache wB.PivotCaches.Create(SourceType:=xlDatabase, _
                                      SourceData:="'Sheet Name'!" & Range("Named_Range").Address, _
                                      Version:=xlPivotTableVersion14)   'xlPivotTableVersion12
            Set pC = pT.PivotCache
            bCreated = True
        Else
            If pT.CacheIndex <> pC.Index Then pT.CacheIndex = pC.Index
        End If
    Next pT
Next wS

'Save to delete unused Pivot Caches
wB.Save
End Sub

Upvotes: 2

Related Questions