VBA Delete Pivot Table If Exists

VBA Delete Pivot Table if Exists in Excel. First, we need to check whether the table exists or not and then we must clear or delete the Pivot table if it exists using VBA in Excel.

Table of Contents

Objective
VBA Delete Pivot Table if Exists
VBA Delete Pivot Table by Name in Workbook
Find and Delete Pivot Table if Exists in Worksheet
Instructions to Run VBA Macro Code
Other Useful Resources

Let us see how to delete or close pivot table if exists on the Worksheet using VBA. In the below procedure ‘Data’ is a Pivot table data sheet name. And ‘PivotTable1’ represents the name of the pivot table which we want to delete. You can change pivot table name and Worksheet name in the below VBA Procedure.

'VBA Delete Pivot Table if Exists
Sub VBAF1_Vba_Delete_Pivot_Table_If_Exists()
    
    'Variable Declaration
    Dim wsWorksheet As Worksheet
  
    'Define Pivot Data Sheet
    Set wsWorksheet = Worksheets("Data")
        
    On Error Resume Next
    'Delete Pivot Table
    wsWorksheet.PivotTables("PivotTable2").TableRange2.Clear
   
End Sub

Output: We notice that pivot table has deleted from the Worksheet which was available.
If Specified pivot table is not available on the worksheet, it throws following run time error ‘1004’ message as shown in the following screenshot.

Note: To avoid this error, we must add ‘On Error Resume Next’ statement to the code as shown below.

VBA Delete Pivot Table by Name in Workbook

We have seen above example how to delete a specific pivot table in a Worksheet. Now let us see how to delete pivot table by name in a Workbook. In the below procedure we loop through all Worksheets in a Workbook and search for the pivot table name ‘PivotTable5’ and deletes it from the Workbook.

'Find and Delete Specific Pivot Table if Exists in Workbook
Sub VBAF1_Find_Delete_Pivot_Table_If_Exists_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
        
            If PtPivotTable = "PivotTable5" Then
                'Delete Pivot Table
                PtPivotTable.TableRange2.Clear
                MsgBox "Found & deleted specific pivot table in Workbook.", vbInformation, "VBAF1"
                Exit Sub
            End If
            
        Next PtPivotTable
    
    Next wsWorksheet
       
End Sub

Output: Here is a output screenshot of the above VBA code.

Find and Delete Pivot Table if Exists in Worksheet

Find and delete or close pivot table if exists in a Worksheet. Where ‘Data’ is a pivot table data sheet name. And ‘PivotTable1’ represents the name of the pivot table which we want to delete. The below VBA code loops through all pivot tables in a Worksheet. Looks for specified pivot table delete or closes it.

'Find and Delete Pivot Table if Exists in Worksheet
Sub VBAF1_Find_Delete_Pivot_Table_If_Exists_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
        
        If PtPivotTable = "PivotTable1" Then
            'Delete Pivot Table
            PtPivotTable.TableRange2.Clear
            MsgBox "Found & deleted pivot table in Worksheet.", vbInformation, "VBAF1"
            Exit Sub
        End If
        
    Next PtPivotTable
       
End Sub

Output: Here is a screenshot for your reference.