Add TableStyle To TableStyles Collection Error

Hi all,

Using Excel 2013.

Trying to add a TableStyle to the TableStyles Collection in a workbook.

I receive this error message - not sure why?

Thx
w

Run-time error '5':
Invalid procedure call or argument

With wb
        For Each ts In .TableStyles
            If ts.ShowAsAvailableTableStyle Then
                If Left(ts.Name, 2) = "ts" Then
                    .TableStyles.Add ("tsCustom" & Mid(ts.Name, 3, Len(ts.Name) - 2))
                End If

            End If
            Next ts
    End With

Bump.

thx
w

Does the name you're trying to add match an existing style?

Thanks Rory,

I manually created a couple of Styles and named them tsData1, tsData2 and tsData3
I really just want to figure out how to rename them using VBA.
I did not see that option so I thought I could Add new Styles named tsCustomData1,....
And then delete the original Custom Styles

thx
w

Your code works fine for me unless the code is trying to add a style using a name that matches an existing name.

Thanks Rory,

My problem was that after trying to add, i tried listing all TableStyles and I did not see the new Styles because i was using ts.ShowAsAvailableTableStyle.
Apparently when adding a TableStyle it is not given a "Show" Proporty?

• .ShowAsAvailablePivotTableStyle
• .ShowAsAvailableSlicerStyle
• .ShowAsAvailableTableStyle

thx
w

You might try duplicating instead:

    With wb
        For Each ts In .TableStyles
            If ts.ShowAsAvailableTableStyle Then
                If Left(ts.Name, 2) = "ts" Then
                    On Error Resume Next
                    ts.Duplicate "tsCustom" & Mid(ts.Name, 3, Len(ts.Name) - 2)
                    If Err.Number = 0 Then ts.Delete
                    Err.Clear
                    On Error GoTo 0
                End If

            End If
        Next ts
    End With

Note that if your existing styles are in use, this will revert the tables to the default style.