How to apply a custom style to a PivotTable using VBA?

To apply a custom style to a PivotTable using VBA, you’ll need to first ensure that your custom style is created and available in Excel. Once you have your style ready, you can use VBA to apply it to your PivotTable. Here is a step-by-step guide on how to do this:

Sub ApplyCustomPivotTableStyle()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim ptStyleName As String

    ' Set the worksheet containing the PivotTable
    Set ws = ThisWorkbook.Sheets("YourSheetName")
    
    ' Set the PivotTable
    Set pt = ws.PivotTables("YourPivotTableName")
    
    ' Define your custom style name
    ptStyleName = "YourCustomStyleName"

    ' Apply the custom style to the PivotTable
    On Error Resume Next
    pt.TableStyle2 = ptStyleName
    If Err.Number <> 0 Then
        MsgBox "The specified PivotTable style could not be applied. Please check the style name."
    End If
    On Error GoTo 0
End Sub

Create a Custom Style in Excel:

Go to an existing PivotTable.
Navigate to the “Design” tab in the Excel Ribbon.
Click on “New PivotTable Style” to create a custom style.
Name and configure your style as desired.

Apply the Custom Style Using VBA:

Open the Visual Basic for Applications (VBA) editor by pressing `Alt` + `F11`.
Insert a new module by right-clicking on any existing module or the workbook in the Project Explorer, then selecting `Insert > Module`.
Enter the following VBA code to apply your custom style:

Modify the Code:

Replace `”YourSheetName”` with the actual name of the sheet where your PivotTable resides.
Replace `”YourPivotTableName”` with the actual name of your PivotTable. You can usually find this name in the PivotTable Options.
Replace `”YourCustomStyleName”` with the name of your custom style.

Run the Macro:

Close the VBA editor and go back to Excel.
Press `Alt` + `F8` to open the Macro dialog box.
Select `ApplyCustomPivotTableStyle` and click `Run`.

This VBA script will apply your specified custom style to the chosen PivotTable. Make sure your custom style name is typed exactly as it is in Excel, including case sensitivity. If the style is not found, the macro will show a message box with an error message.

facebooklinkedin