How can I change the table style for all tables in a workbook simultaneously?

Hi, I have some stats software that exports tables to Excel but can only do so in one of the standard Excel table styles (e.g. Light 1, Medium 1 etc.). I have created a custom style that better f...

Macros & VBA

tips and tricks

Wyn Hopkins

Mar 02, 2017

Hi Adam

If you set a custom table style and call it MyStyle for example then use this code

Sub FormatAllTables()


Dim wks As Worksheet
Dim tbl As ListObject



    For Each wks In Worksheets
    
            For Each tbl In wks.ListObjects
                        
                tbl.TableStyle = "MyStyle"
                        
            Next tbl
        
    Next wks



End Sub

Cheers

Wyn Hopkins

MVP

Mar 02, 2017

Hi Adam

If you set a custom table style and call it MyStyle for example then use this code

Sub FormatAllTables()


Dim wks As Worksheet
Dim tbl As ListObject



    For Each wks In Worksheets
    
            For Each tbl In wks.ListObjects
                        
                tbl.TableStyle = "MyStyle"
                        
            Next tbl
        
    Next wks



End Sub

Cheers

Adam Drummond

Copper Contributor

Mar 03, 2017

Thanks, that's exactly what I was looking for!

Wyn Hopkins
MVP
Mar 03, 2017
You're welcome

Forum Discussion

How can I change the table style for all tables in a workbook simultaneously?

Resources