VBA Delete All Pivot Tables

VBA Delete All Pivot Tables in a Worksheet. When we have multiple pivot tables on the worksheet or in a Workbook, it is difficult to delete one by one pivot table. So let us see how to delete all pivot tables using VBA in Excel. You can refer comments to understand each statement in the procedure.

Table of Contents

Objective
VBA Delete All Pivot Tables on the Worksheet
Delete All Pivot Tables in a Workbook using VBA
Instructions to Run VBA Macro Code
Other Useful Resources

VBA Delete All Pivot Tables on the Worksheet

Let us see how to delete or close all pivot tables which are available on the Worksheet. In the below procedure ‘Data’ is a pivot table data sheet name. We use for each loop here to loop all available pivot tables in the worksheet and then deletes it.

'Delete all available Pivot Tables in a Worksheet
Sub VBAF1_VBA_Delete_All_Pivot_Tables_In_Worksheet()
    
    'Variable Declaration
    Dim wsWorksheet As Worksheet
    Dim PtPivotTable As PivotTable
  
    'Define Pivot Data Sheet
    Set wsWorksheet = Worksheets("Data")

   'Loop through all Pivot Tables in a Worsheet
    For Each PtPivotTable In wsWorksheet.PivotTables
        'Delete Pivot Table
        PtPivotTable.TableRange2.Clear
    Next PtPivotTable
   
End Sub

Output: We notice that all the pivot tables are deleted from the specified Worksheet which are available and displays following message on the screen.

Delete All Pivot Tables in a Workbook using VBA

The following example VBA macro code helps us to delete all available pivot tables in a workbook. In the below procedure we loop through all worksheets and pivot tables in the Workbook and then closes available pivot tables from the Worksheet.

'Delete all available Pivot Tables in a Workbook
Sub VBAF1_VBA_Delete_All_Pivot_Tables_In_Workbook()
    
    'Variable Declaration
    Dim wsWorksheet As Worksheet
    Dim PtPivotTable As PivotTable
  
    'Define Pivot Data Sheet
    Set wsWorksheet = Worksheets("Data")
        
   'Loop through all Worksheets in a Workbook
    For Each wsWorksheet In ActiveWorkbook.Worksheets
        
        'Loop through all Pivot Tables in a Worsheet
        For Each PtPivotTable In wsWorksheet.PivotTables
            'Delete Pivot Table
            PtPivotTable.TableRange2.Clear
        Next PtPivotTable
        
    Next wsWorksheet
    
End Sub

Output: Upon successful execution of the above procedure or VBA code it displays following message.