Create multiple pivot caches

[chrill]

Hi

I wan't to create two different pivotcaches from two named ranges. I then want to use those pivot caches in different pivot tables by only change to the loaded cache. Is this possible?

Today I have the code below but it needs to much memory. Think it creates lots of different caches.

For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
        py = pt.SourceData
        If Right(py, 2) = "SA" Then
            pt.ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:="[MRBT_Light.xlsx]SA!SA")
        Else
            If Right(py, 2) = "CA" Then
            pt.ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:="[MRBT_Light.xlsx]CA!CA ")
            End If
        End If
        pt.RefreshTable
    Next pt
Next ws

BR
Chrill