VBA To Change Pivot Table Fields Format

By just a single click, we can change pivot table field value format to any format.

Codes to change pivot table field default format to number format:

    Dim pvName As String, pvfName As String

    

    pvName = ActiveCell.PivotTable.Name

    pvfName = ActiveCell.PivotField.Name

    

    With ActiveSheet.PivotTables(pvName).PivotFields(pvfName)

        .NumberFormat = "#,##0_);[Red](#,##0)"

    End With

Codes to change pivot table field default format to percentage format:

    Dim pName As String, pfName As String

    

    pName = ActiveCell.PivotTable.Name

    pfName = ActiveCell.PivotField.Name

    

    With ActiveSheet.PivotTables(pName).PivotFields(pfName)

        .NumberFormat = "0%;[Red]-0%"

    End With

Note: Adjust .NumberFormat to your desire format.

VBA to Remove Subtotal from Pivot Table

The code below remove/ disable/ do not show subtotal of each row labels. At the same, it repeats each row labels. It can be used as a QAT (Quick Access Toolbar) or can be called in module when you put the codes/ function in the top or above of your macro codes.

Sub PvtNoSubTtl()
         
            Dim pt As PivotTable
            Dim pf As PivotField
            On Error Resume Next
         
            For Each pt In ActiveSheet.PivotTables
                pt.RowAxisLayout xlTabularRow
             
            Next pt
         
            For Each pt In ActiveSheet.PivotTables
                For Each pf In pt.PivotFields
                    pf.RepeatLabels = True
                    pf.Subtotals(1) = True
                    pf.Subtotals(1) = False
                Next pf
            Next pt

End Sub

VBA to Select Dynamic Pivot Table Data Source

Create a pivot table that is based on a dynamic data source -- that is a range that adjusts automatically, if data is added or removed. Below codes are able to create a dynamic pivot table that will work on data that varies in the number of rows or columns.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1!" & Sheets("Sheet1").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), Version:=6).CreatePivotTable TableDestination:="", TableName:="PivotTable1", DefaultVersion:=6

Alternatively, a dynamic range is a good solution for excel without vba.

1. Create a new Name Manager to define the range. Basic rule for name manager is no space. In this case, my name is "all_data'
2. In the 'Refers to' box, use the following formula, adapted for your own data:

OFFSET(OSR!$A$1,0,0,COUNTA(OSR!$A:$A),11)

Where:

OSR is worksheet name of the pivot data.
Range $A$1 is  the first cell in the header row.
$A:$A is count the number of non-blank cells in column A - change this to a column that will always have an entry for each row.
11 is the number of columns across your data is - e.g. if you have columns A to K filled then this number would be 11.