Pivot Cache in one one workbook, Pivot table in an

Question

I have created a Pivot Cache in (say)Workbook 'sourceBook.xlsx' in sheet 'sourceSheet'. I am trying to create a Pivot table in a different workbook 'destinBook.xlsx' in sheet 'destinSheet'.

Dim pCache As PivotCache
Dim pTable As PivotTable
Dim pRange As Range

Module2.hc_book.Activate
Set pRange = ActiveSheet.Range(hc_pidCol & "1:" & hc_pidCol & hc_lastRow)

Set pCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, pRange)
Module2.mt_book.Activate
Set tempSheet = Worksheets.Add
tempSheet.Select
Set pTable = ActiveSheet.PivotTables.Add(pCache, Range("A1"), "MyPivotTable")

The code is giving me error in the last line - "Invalid procedure call or argument", where I am setting the pTable. The code works fine for destination in the same sheet. So, please let me know where I am making the mistakes.

Answer 1

A PivotCache is the memory cache for a PivotTable report. This memory cache is needed for the PivotTable to function in the first place.

You could create a PivotCache from data in the current workbook, but it would have to be part of the PivotCaches in the new workbook, in order to create a PivotTable based of off it.

Since the PivotCache is not available in the new Workbook.PivotCaches, you cannot create a PivotTable in that workbook of off it and that's why your code doesn't run.

This runs fine:

Sub test()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pRange As Range
    Dim pc As PivotCache
    Dim pt As PivotTable

    Set wb = Workbooks.Add
    Set ws = wb.Worksheets(1)

    Set pRange = ThisWorkbook.Sheets("Sheet1").Range("A1:C3")

    Set pc = wb.PivotCaches.Create(xlDatabase, pRange)
    Set pt = ws.PivotTables.Add(pc, Range("F2"), "MyPivotTable")

End Sub

This does not work:

Sub test()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pRange as Range
    Dim pc As PivotCache
    Dim pt As PivotTable

    Set wb = Workbooks.Add
    Set ws = wb.Worksheets(1)

    Set pRange = ThisWorkbook.Sheets("Sheet1").Range("A1:C3")

    Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, pRange) 'Cache in ThisWorkbook
    Set pt = ws.PivotTables.Add(pc, Range("F2"), "MyPivotTable") 'Cache unavailable, error 5 - Invalid Procedure Call or Argument. 

End Sub

The invalid argument for this error is the pc object.

In short: PivotCache object needs to be part of the PivotCaches collection of the Workbook where you wish to create the PivotTable

EDIT: Just to clarify: A PivotCache is an in memory object. It has nothing to do with the source where you got the data from. This source can indeed be the range in your first workbook, or the results of a SQL query, or a CSV-file, whatever you choose.

EDIT 2: A very basic implementation of "copying" a pivotCache to a new workbook would be:

Sub CopyPivotCache()
    Dim wb As Workbook
    Dim InitialPivotCache As PivotCache
    Dim CopyPivotCache As PivotCache

    Set wb = Workbooks.Add
    Set InitialPivotCache = ThisWorkbook.PivotCaches(1)
    Set CopyPivotCache = wb.PivotCaches.Create(InitialPivotCache.SourceType, InitialPivotCache.SourceData)
End Sub