pivot cache delete in vba

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,890
You can change the PivotTableCache using this code. Modify the Version constants to match your version of excel.:

VBA Code:
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

VBA Code:
Worksheets("Sheet2").PivotTables("PivotT2").TableRange2.Cells.Clear

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

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,447
Messages
5,636,326
Members
416,912
Latest member
danluk12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top