SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-49393%22%20slang%3D%22en-US%22%3EHow%20can%20I%20change%20the%20table%20style%20for%20all%20tables%20in%20a%20workbook%20simultaneously%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-49393%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%26nbsp%3Bhave%20some%20stats%20software%20that%20exports%20tables%20to%20Excel%20but%20can%20only%20do%20so%20in%20one%20of%20the%20standard%20Excel%20table%20styles%20(e.g.%20Light%201%2C%20Medium%201%20etc.).%20I%20have%20created%20a%20custom%20style%20that%20better%20fits%20with%20my%20company's%20usual%20outputs%20which%20we%20would%20need%20to%20apply%20to%26nbsp%3Ball%20tables.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20it%20is%20not%20possible%20to%20do%20this%20by%20selecting%20all%20sheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20macro%20or%20VBA%20script%20that%20can%20let%20me%20change%20the%20style%20of%20all%20tables%20in%20a%20workbook%20at%20the%20same%20time%3F%20Otherwise%20we%20will%20have%20to%20go%20through%20150%2B%20sheets%20and%20make%20the%20change%20one%20by%20one.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-49393%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20%26amp%3B%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETips%20and%20Tricks%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-50071%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20change%20the%20table%20style%20for%20all%20tables%20in%20a%20workbook%20simultaneously%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-50071%22%20slang%3D%22en-US%22%3EYou're%20welcome%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-50070%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20change%20the%20table%20style%20for%20all%20tables%20in%20a%20workbook%20simultaneously%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-50070%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%20that's%20exactly%20what%20I%20was%20looking%20for!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-49690%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20change%20the%20table%20style%20for%20all%20tables%20in%20a%20workbook%20simultaneously%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-49690%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Adam%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20set%20a%20custom%20table%20style%20and%20call%20it%20MyStyle%20for%20example%20then%20use%20this%20code%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20FormatAllTables()%0A%0A%0ADim%20wks%20As%20Worksheet%0ADim%20tbl%20As%20ListObject%0A%0A%0A%0A%20%20%20%20For%20Each%20wks%20In%20Worksheets%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20For%20Each%20tbl%20In%20wks.ListObjects%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20tbl.TableStyle%20%3D%20%22MyStyle%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20Next%20tbl%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20Next%20wks%0A%0A%0A%0AEnd%20Sub%3C%2FPRE%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1433386%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20change%20the%20table%20style%20for%20all%20tables%20in%20a%20workbook%20simultaneously%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1433386%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Wyn%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20posting%20this%20code.%20I'm%20looking%20to%20modify%20it%20slightly%20to%20only%20change%20the%20first%20table%20on%20each%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20sheet%20of%20my%20workbook%20has%20two%20tables%2C%20and%20I%20would%20like%20to%20change%20the%20first%20one%20on%20each%20sheet%20to%20my%20custom%20table%20style%2C%20while%20leaving%20the%202nd%20table%20formatted%20as%20it%20was%20previously.%20Do%20you%20know%20how%20I%20could%20modify%20this%20code%20to%20do%20that%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much%20in%20advance%20for%20any%20direction%20you%20can%20provide.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1435041%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20change%20the%20table%20style%20for%20all%20tables%20in%20a%20workbook%20simultaneously%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1435041%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3EYou%20would%20most%20likely%20have%20to%20name%20the%20tables%20in%20some%20consistent%20manner%20e%2Cg%2C%20myTblSales%2C%20myTblCosts%20so%20the%20VBA%20can%20check%20say%20the%20first%205%20letters%20of%20the%20table%20and%20if%20its%20myTbl%20then%20do%20the%20change%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1482598%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20change%20the%20table%20style%20for%20all%20tables%20in%20a%20workbook%20simultaneously%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1482598%22%20slang%3D%22en-US%22%3EThanks%20!%20That%20worked%20perfectly.%20I%20added%20a%20couple%20of%20extra%20lines%20to%20also%20change%20font%2C%20as%20that%20seems%20to%20not%20be%20changed%20with%20the%20TableStyle%20alone..%20(%3F%3F)%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

6 Replies
Highlighted
Best Response confirmed by Adam Drummond (New Contributor)
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

Highlighted

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

Highlighted
Highlighted

@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.

Highlighted
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
Highlighted
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.. (??)