Macro to Delete Pivot Tables

These VBA macros remove PivotTables from the active sheet or the whole workbook, or turn them into plain values so the numbers stay but the PivotTable doesn’t.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Delete PivotTables on the active sheet

Sub DeletePivotsSheet()
    Dim i As Long
    For i = ActiveSheet.PivotTables.Count To 1 Step -1
        ActiveSheet.PivotTables(i).TableRange2.Clear
    Next i
End Sub

It counts down from the last PivotTable, so removing one doesn’t upset the loop. TableRange2 includes the filter area above the PivotTable.

Delete every PivotTable in the workbook

Sub DeletePivotsWorkbook()
    Dim ws As Worksheet, i As Long
    For Each ws In Worksheets
        For i = ws.PivotTables.Count To 1 Step -1
            ws.PivotTables(i).TableRange2.Clear
        Next i
    Next ws
End Sub

Keep the numbers, remove the PivotTable

Converts each PivotTable on the sheet into plain values, for sending a report without the source data:

Sub PivotsToValues()
    Dim i As Long, r As Range
    For i = ActiveSheet.PivotTables.Count To 1 Step -1
        Set r = ActiveSheet.PivotTables(i).TableRange2
        r.Copy
        r.PasteSpecial xlPasteValues
    Next i
    Application.CutCopyMode = False
End Sub

Without a macro

  1. Click inside the PivotTable.
  2. Go to PivotTable Analyze → Select → Entire PivotTable.
  3. Press Delete.

Things to know

  • Deleting a PivotTable can’t be undone. Save a copy first.
  • The source data isn’t affected.
  • To also stop the file carrying hidden PivotTable data, save after deleting.

Related macros