How to reset a PivotTable layout to default using VBA?

To reset a PivotTable layout to its default settings using VBA, you need to modify several properties of the PivotTable object. Here’s a general approach to accomplish this. This script will clear any customizations such as filters, row/column fields, and values, essentially resetting the PivotTable to its initial state when first created.

Here’s a simple VBA script you can use:

Sub ResetPivotTableLayout()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField

    ' Set to the worksheet containing your PivotTable
    Set ws = ThisWorkbook.Sheets("YourSheetName")
    
    ' Set to the name of your PivotTable
    Set pt = ws.PivotTables("YourPivotTableName")
    
    ' Clear all filters and fields
    On Error Resume Next ' Ignore errors for fields that are not available

    ' Clear all filters
    pt.ClearAllFilters

    ' Remove all row fields
    For Each pf In pt.RowFields
        pt.PivotFields(pf.Name).Orientation = xlHidden
    Next pf

    ' Remove all column fields
    For Each pf In pt.ColumnFields
        pt.PivotFields(pf.Name).Orientation = xlHidden
    Next pf

    ' Remove all data fields
    For Each pf In pt.DataFields
        pt.PivotFields(pf.Name).Orientation = xlHidden
    Next pf

    ' Remove all page fields (filters)
    For Each pf In pt.PageFields
        pt.PivotFields(pf.Name).Orientation = xlHidden
    Next pf

    On Error GoTo 0 ' Resume normal error handling

    ' Optional: Refresh the PivotTable to reflect changes
    pt.RefreshTable

    MsgBox "PivotTable layout has been reset to default.", vbInformation
End Sub

Key Steps Explained:

Reference the Worksheet: Set the `ws` variable to the worksheet containing your PivotTable by replacing `”YourSheetName”` with the actual sheet name.
Reference the PivotTable: Set the `pt` variable to your PivotTable by replacing `”YourPivotTableName”` with the actual PivotTable name.
Clear Filters and Fields:

`pt.ClearAllFilters` is used to clear any filters applied to the PivotTable.
Iterate over each field in `RowFields`, `ColumnFields`, `DataFields`, and `PageFields` to set their `Orientation` to `xlHidden`, which effectively removes them from the PivotTable.

Refresh the PivotTable: Optionally refresh the PivotTable to apply the changes by calling `pt.RefreshTable`.
Messaging: Display a message box to indicate the reset operation has been completed successfully.

Make sure to adjust the placeholders `”YourSheetName”` and `”YourPivotTableName”` to match your actual worksheet name and PivotTable name. Always back up your data before running scripts to prevent any accidental data loss.

facebooklinkedin