Q: Use VBA to create a new Pivot TableStyle

Question

I recorded a macro to create a new Pivot Table style so that whenever I create a new worksheet, this Pivot Table Style is added as the default for the Workbook. However, it does not seem to work when I try to run it on a new workbook. At first, I thought it might be the names (i.e. Sheet1), but even when everything matches it errors out on the first line. I hate having to go in and add this new pivot table style to every report I make, so if anyone has any tips, I'd greatly appreciate it. I'm a complete novice at VBA, so if there are any suggestions for making this code shorter, that would be of great help too!

EDIT: The code does not have curly quotations - that was me renaming it for posting.

Additional Edit: This is the error I get: VBA Error: Run-time error '5': invalid procedure call or arguement

When I hit Debug, it takes me to the first line of code, which is highlighted in yellow: ActiveWorkbook.TableStyles.Add (“Overview”)

Sub Overview_Pivot_Format()
'
' Overview_Pivot_Format Macro
'

'
    ActiveWorkbook.TableStyles.Add (“Overview”)
    With ActiveWorkbook.TableStyles(“Overview”)
        .ShowAsAvailablePivotTableStyle = True
        .ShowAsAvailableTableStyle = False
        .ShowAsAvailableSlicerStyle = False
        .ShowAsAvailableTimelineStyle = False
    End With
    ActiveWorkbook.DefaultPivotTableStyle = “Overview”
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlWholeTable).Borders(xlEdgeTop)
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlWholeTable).Borders(xlEdgeBottom)
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlWholeTable).Borders(xlEdgeLeft)
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlWholeTable).Borders(xlEdgeRight)
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlWholeTable).Borders(xlInsideVertical)
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlWholeTable).Borders(xlInsideHorizontal)
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlHeaderRow).Interior
        .Color = 15658734
        .TintAndShade = 0
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlHeaderRow).Borders(xlEdgeTop)
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlHeaderRow).Borders(xlEdgeBottom)
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlHeaderRow).Borders(xlEdgeLeft)
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlHeaderRow).Borders(xlEdgeRight)
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlHeaderRow).Borders(xlInsideVertical)
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlHeaderRow).Borders(xlInsideHorizontal)
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlTotalRow).Font
        .FontStyle = "Bold"
        .TintAndShade = 0
        .ThemeColor = xlThemeColorDark1
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlTotalRow).Interior
        .Color = 6697728
        .TintAndShade = 0
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlSubtotalRow1).Font
        .FontStyle = "Bold"
        .TintAndShade = 0
        .ThemeColor = xlThemeColorDark1
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlSubtotalRow1).Interior
        .Color = 6697728
        .TintAndShade = 0
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlSubtotalRow2).Font
        .FontStyle = "Bold"
        .TintAndShade = 0
        .ThemeColor = xlThemeColorDark1
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlSubtotalRow2).Interior
        .Color = 6697728
        .TintAndShade = 0
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlSubtotalRow3).Font
        .FontStyle = "Bold"
        .TintAndShade = 0
        .ThemeColor = xlThemeColorDark1
    End With
    With ActiveWorkbook.TableStyles(“Overview”).TableStyleElements( _
        xlSubtotalRow3).Interior
        .Color = 6697728
        .TintAndShade = 0
    End With
End Sub

Does your code actually have the smart (curly) quotes around Overview? It should have straight quotes ". — BigBen
In VBA smart quotes are a legal part of a variable name, so VBA sees a variable with the literal name “Overview” and so it's treated as an undeclared variable. Using Option Explicit (and you should always use that) will warn you about these types of errors. excel-easy.com/vba/examples/option-explicit.html — Tim Williams
Hi all, sorry for the confusion! In the code it is straight quotes - I had to edit the name for posting so it shows up with curly quotes instead. — Maria Hernandez
"it errors out on the first line" is not a really useful description of exactly what happens when you run your code. — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2021-08-16T21:58:40

The below worked for me:

Sub Overview_Pivot_Format()
    
    Dim ts As TableStyle, wb As Workbook
    
    Set wb = ActiveWorkbook 'workbook to be updated
    
    On Error Resume Next              'ignore error if no style found in next line
    wb.TableStyles("OverView").Delete 'in case already present
    On Error GoTo 0                   'stop ignoring errors
    Set ts = wb.TableStyles.Add("Overview") 'get a reference to the added style
    
    With ts
        .ShowAsAvailablePivotTableStyle = True
        .ShowAsAvailableTableStyle = False
        .ShowAsAvailableSlicerStyle = False
        .ShowAsAvailableTimelineStyle = False
    End With
    wb.DefaultPivotTableStyle = ts
    'set properties by calling the 3 subs below...
    DoBorders ts.TableStyleElements(xlWholeTable)
    
    DoInterior ts.TableStyleElements(xlHeaderRow), 15658734
    DoBorders ts.TableStyleElements(xlHeaderRow)
    
    DoInterior ts.TableStyleElements(xlTotalRow), 6697728
    DoFont ts.TableStyleElements(xlTotalRow)
    
    DoInterior ts.TableStyleElements(xlSubtotalRow1), 6697728
    DoFont ts.TableStyleElements(xlSubtotalRow1)
    
    DoInterior ts.TableStyleElements(xlSubtotalRow2), 6697728
    DoFont ts.TableStyleElements(xlSubtotalRow2)
    
    DoInterior ts.TableStyleElements(xlSubtotalRow3), 6697728
    DoFont ts.TableStyleElements(xlSubtotalRow3)
     
End Sub

'next 3 sub take care of updating the styles...
Sub DoFont(tse As TableStyleElement)
    With tse.Font
        .FontStyle = "Bold"
        .TintAndShade = 0
        .ThemeColor = xlThemeColorDark1
    End With
End Sub

Sub DoBorders(tse As TableStyleElement)
    With tse.Borders() 'no need to set individually...
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
        .LineStyle = xlNone
    End With
End Sub

Sub DoInterior(tse As TableStyleElement, clr As Long)
    With tse.Interior
        .Color = clr
        .TintAndShade = 0
    End With
End Sub

Q: Use VBA to create a new Pivot TableStyle

1 Answers