Forum Discussion

Adam Drummond's avatar
Adam Drummond
Copper Contributor
Mar 01, 2017
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 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

Macros & VBA
tips and tricks
  • Wyn Hopkins's avatar
    Wyn Hopkins
    Mar 02, 2017

    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

6 Replies

  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Mar 02, 2017

    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

    • Max_W13's avatar
      Max_W13
      Copper Contributor
      Jun 23, 2020
      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.. (??)
    • JoeTorretto's avatar
      JoeTorretto
      Copper Contributor
      Jun 02, 2020

      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.

      • Wyn Hopkins's avatar
        Wyn Hopkins
        MVP
        Jun 03, 2020
        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

Resources