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

[Adam Drummond]

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 fits with my company's usual outputs which we would need to apply to all tables.

However, it is not possible to do this by selecting all sheets.

Is there a macro or VBA script that can let me change the style of all tables in a workbook at the same time? Otherwise we will have to go through 150+ sheets and make the change one by one.

Many thanks

[#2 · Accepted solution]

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

[#3]

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

[#4]

You're welcome

[#5]

@Wyn Hopkins 

Hi Wyn,

Thanks for posting this code. I'm looking to modify it slightly to only change the first table on each sheet.

Each sheet of my workbook has two tables, and I would like to change the first one on each sheet to my custom table style, while leaving the 2nd table formatted as it was previously. Do you know how I could modify this code to do that?

Thanks so much in advance for any direction you can provide.

[#6]

Hi
You would most likely have to name the tables in some consistent manner e,g, myTblSales, myTblCosts so the VBA can check say the first 5 letters of the table and if its myTbl then do the change

[#7]

Thanks ! That worked perfectly. I added a couple of extra lines to also change font, as that seems to not be changed with the TableStyle alone.. (??)