Excel VBA pivot table cache method error

Question

I am trying to execute a macro which basically creates a pivot table from a dynamic range and then makes use of this data.

The pivot table is kept in a sheet, and every time the macro is executed it deletes the pivot table in that sheet, then updates the data range and generates the pivot table again with the new range.

However, the part of the code that tries to generate a cache causes an error:

"Invalid procedure call or argument".

At first it failed, so I added a piece refreshing the existing (I think so) cache. But I still receive the mentioned error.

The code is as below.

Sub Weeks_Coverage_Calc()
'Creates a pivot table to calculate stock weeks coverage for every item

Dim sht As Worksheet
Dim Activesht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
Dim lastRow As Integer
Dim Rng As Range
Dim Row As Range

'Clear the worksheet for pivot table
    Set sht = ActiveWorkbook.Sheets("Weeks Coverage")
    For Each pvt In sht.PivotTables
    pvt.PivotSelect "", xlDataAndLabel, True
    Selection.Delete Shift:=xlToLeft
    Next pvt
'Set Transfer sheet as active sheet
 Set Activesht = ActiveWorkbook.Sheets("Transfer")

'Determine the dynamic range
 lastRow = Activesht.Range("B1000000").End(xlUp).Row
 Set Rng = Activesht.Range("B4:AF" & lastRow)


'Determine the data range you want to pivot
  SrcData = ActiveSheet.Name & "!" & Rng.Address(ReferenceStyle:=xlR1C1)

'Initialize the cell to start the table
  StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

'Create Pivot Cache from Source Data
  Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)


    'Refresh pivot cache
        For i = 1 To Worksheets("Weeks Coverage").PivotTables.Count
        Worksheets("Weeks Coverage").PivotTables(i).PivotCache.Refresh
        Next i


'Create Pivot table from Pivot Cache
    Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=StartPvt, _
    TableName:="PivotTable1")

'Add fields to Pivot Table

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Item ID")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Stok"), "Sum of Stok", xlSum

    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Sales"), "Sum of Sales", xlSum

Should be SrcData = ActiveSht.Name not SrcData = ActiveSheet.Name — Rory

Shai Rado Shai Rado · Accepted Answer · 2017-07-24T15:23:19

Try setting up the PivotTable and PivotCache with the code below:

'Determine the data range you want to pivot
SrcData = Rng.Address(False, False, xlA1, xlExternal)

'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:=SrcData)

'Create Pivot table from Pivot Cache
Set pvt = sht.PivotTables.Add(PivotCache:=pvtCache, TableDestination:=sht.Range("A3"), _
            TableName:="PivotTable1")

Excel VBA pivot table cache method error

1 Answers