pivot cache delete in vba

[ER_Neha]

Hello friends, I am trying to construct a pivot table, but once I run the code for pivotcache creation and seeing results in immediate window creates an error 1004 for the second time run of the code.

I am trying to seek an answer that can help me clear the cache in the starting of the pivot table, and construct a new one as I write the code.
Please help
I already tried:
1. activeworkbook.pivotcache.delete (doesnt work)
2.missingitems

Sub pivot1()

    Dim sh As Worksheet
    Dim shnew As Worksheet
        
  Set sh = ActiveWorkbook.Sheets("Sheet1")
    Set shnew = ActiveWorkbook.Sheets("Sheet2")

Debug.Print ActiveWorkbook.PivotCaches.Count
Debug.Print ActiveWorkbook.PivotTables.Count
    sh.Select
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table1", Version:=6).CreatePivotTable TableDestination:=shnew.Range("a3"), _
        TableName:="PivotT1", DefaultVersion:=6

    Debug.Print ActiveWorkbook.PivotCaches.Count
End Sub

 

[pbornemeier]

You can change the PivotTableCache using this code. Modify the Version constants to match your version of excel.:

Sub RefreshPivotTableSource()
    'XL 2007
    Dim lX As Long
    Dim sSourceSheet As String
    Dim sPivotTableSheet As String
    
    sSourceSheet = "Data"
    sPivotTableSheet = "PT"
    
    For lX = 1 To Worksheets(sPivotTableSheet).PivotTables.Count
        Worksheets(sPivotTableSheet).PivotTables(lX).ChangePivotCache _
            ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
             sSourceSheet & "!" & ActiveWorkbook.Worksheets(sSourceSheet).Range("A1"). _
             CurrentRegion.Address(ReferenceStyle:=xlR1C1), Version:=xlPivotTableVersion10)
    Next
    
End Sub

To delete a PivotCache, delete the associated table

Worksheets("Sheet2").PivotTables("PivotT2").TableRange2.Cells.Clear

And to get some info about PT/PC in your workbook:

Sub WorkbookPT_PCDetails()

    Dim wks As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable
        
    Debug.Print Now() & " -------------------"
    Debug.Print ActiveWorkbook.PivotCaches.Count & " PivotCache(s) in workbook"
    For Each pc In ActiveWorkbook.PivotCaches
        Debug.Print "PC: #" & pc.Index, pc.SourceData
    Next
    For Each wks In ActiveWorkbook.Worksheets
        If wks.PivotTables.Count > 0 Then
            Debug.Print wks.PivotTables.Count & " PivotTable(s) in: " & "Worksheet: " & wks.Name
            For Each pt In wks.PivotTables
                Debug.Print "    PT: " & pt.Name, "Range: " & pt.TableRange2.Address, "Source Data: " & pt.SourceData
            Next
        End If
    Next
    
End Sub