Create a new Pivot TableStyle

Question

I recorded a macro to create a new Pivot Table style so whenever I create a new worksheet, this Pivot Table Style is added as the default for the Workbook.

It does not work when I try to run it on a new workbook. I thought it might be the names (i.e. Sheet1), but even when everything matches it errors on the first line.

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

This is the error I get:

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

Answer 1

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