Delete All Pivot Tables with Excel VBA

 This Excel VBA procedure will delete all of the pivot tables in a particular worksheet.  Just a point to note though, it works on the activesheet so you need to run the procedure on the sheet where the pivot tables live. It came in handy when I was devising the procedure to Create a Pivot Table with VBA.  As once the procedure is run once the second time the procedure is run the code will fail unless the pivot tables is cleared. If you run this procedure in the attached workbook the pivot tables disappear and running it a second time is fruitless. It will not error out though.

The following YouTube video covers how to remove pivot tables with Excel VBA. The file below is the start Excel file.

RemovePIvots.xlsm

Below is an example of what the pivot table delete code looks like. Be aware that the pivot table is drawing on TableRange2. The importance of TbleRange2 is that it includes all the fields in the pivot table. It is better to use TableRange2 in this situation.

 Sub RemPiv() 'Excel VBA to Delete all pivot tables in a worksheet.
Dim Pt As PivotTable

    For Each Pt In ActiveSheet.PivotTables
        Pt.TableRange2.Clear
    Next Pt

End Sub

While the Excel VBA procedure looks very simple, it is very effective. Attached is a file - I am using Office 365. The following is the end Excel file, while the above is the start file.

Piv File Delete.xlsm

What About Deleting All Pivot Tables in a Workbook

Now let’s say we have a workbook filled with pivot tables what can we do to extend the code so it works on all of the worksheets in the workbook. This is just a matter of creating a loop within a loop for both the sheets in the workbook and the pivot tables within those worksheets. Adding a loop will enable you to cycle through all of the worksheets

Sub RemPiv1() 'Excel VBA to Delete all pivot tables in a workbook.
Dim Pt As PivotTable
Dim sh As Worksheet

    For Each sh In Sheets
        For Each Pt In sh.PivotTables
            Pt.TableRange2.Clear
        Next Pt
    Next sh

End Sub

The above code is very efficient - almost too efficient at deleting pivot tables in a workbook. Everything gets zapped. The removal is complete and only the button remains on the sheet in the workbook. Pivot tables are gone so will need to be rebuilt perhaps with your next bit of VBA code.