- last edited on
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.
03-01-2017 04:06 PMSolution
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
06-02-2020 06:52 AM
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.
06-02-2020 06:44 PM
06-22-2020 08:23 PM