Mar 01 2017
02:21 AM
- last edited on
Jul 25 2018
09:34 AM
by
TechCommunityAP
Mar 01 2017
02:21 AM
- last edited on
Jul 25 2018
09:34 AM
by
TechCommunityAP
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
Mar 01 2017 04:06 PM
SolutionHi 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
Mar 03 2017 01:01 AM
Thanks, that's exactly what I was looking for!
Mar 03 2017 01:03 AM
Jun 02 2020 06:52 AM
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.
Jun 02 2020 06:44 PM
Jun 22 2020 08:23 PM
Mar 01 2017 04:06 PM
SolutionHi 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