Tom’s Tutorials For Excel: Delete all Pivot Tables

Tom’s Tutorials For Excel: Delete all Pivot Tables

You may find the need to delete all pivot tables that are on a worksheet, or that are in the entire workbook.

This first macro deletes all pivot tables on the active worksheet:

Sub DeletePivotTablesWorksheet()
Dim objPT As PivotTable, iCount As Integer
For iCount = ActiveSheet.PivotTables.Count To 1 Step -1
Set objPT = ActiveSheet.PivotTables(iCount)
objPT.PivotSelect ""
Selection.Clear
Next iCount
End Sub

This next macro deletes all pivot tables in the workbook. I prefer to activate the worksheets in turn, which is why the code checks for each worksheet being visible.

Sub DeletePivotTablesWorkbook()
Application.ScreenUpdating = False
Dim objPT As PivotTable, iCount As Integer, iSheet as Integer
For iSheet = 1 to worksheets.Count
If Worksheets(iSheet).Visible = False then worksheets(iSheet).Visible = xlSheetVisible
Worksheets(iSheet).Activate
For iCount = ActiveSheet.PivotTables.Count To 1 Step -1
Set objPT = ActiveSheet.PivotTables(iCount)
objPT.PivotSelect ""
Selection.Clear
Next iCount
Next iSheet
Application.ScreenUpdating = True
End Sub