Forum Discussion
Solved
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...
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 SubCheers
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 SubCheers
- 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.. (??)
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.
- 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
Thanks, that's exactly what I was looking for!
- You're welcome
