Excel Macro VBA looping issues- Formatting all tables-CodePudding

This code is looping through and changing all the table styles but it is not changing all the fonts and sizes. Only the current ws.

Sub Format_Tables()
    
    ' Tables_Format Macro

    Dim ws As Worksheet
    
    Dim tbl As ListObject
    
    Set ws = ActiveSheet
    
    'Loop through each worksheet    
    For Each ws In ActiveWorkbook.Worksheets
    For Each tbl In ws.ListObjects
    
            tbl.TableStyle = "TableStyleMedium9"    
            Cells.Select    
            With Selection.Font
                 .Name = "Garamond"
                 .Size = 12
                 .Strikethrough = False
                 .Superscript = False
                 .Subscript = False
                 .OutlineFont = False
                 .Shadow = False
                 .Underline = xlUnderlineStyleNone
                 .ThemeColor = xlThemeColorLight1
                 .TintAndShade = 0
                 .ThemeFont = xlThemeFontNone
            End With
    
            Selection.Rows.AutoFit    
            Selection.Columns.AutoFit    
            Selection.NumberFormat = "0.000"
    
        Next tbl
    
    Next ws

    'MsgBox (Err.Description)  End Sub

CodePudding user response：

Cells.Select

in a regular code module will only work on the activesheet, so you just format the same sheet repeatedly. Try

With ws.UsedRange.Font

instead (no need to Select)

CodePudding user response：

If you want to change just the data you want to grab the DataBodyRage https://docs.microsoft.com/en-us/office/vba/api/Excel.ListObject.DataBodyRange

If you want to change the headers you use HeaderRowRange https://docs.microsoft.com/en-us/office/vba/api/excel.listobject.headerrowrange

EXAMPLE:

Public Sub FormatTables()    
    Dim ws As Worksheet        
    For Each ws In ActiveWorkbook.Worksheets
        Dim tbl As ListObject
        For Each tbl In ws.ListObjects
    
            tbl.TableStyle = "TableStyleMedium9"    
              
            With tbl.DataBodyRage.Font
                 .Name = "Garamond"
                 .Size = 12
                 .Strikethrough = False
                 .Superscript = False
                 .Subscript = False
                 .OutlineFont = False
                 .Shadow = False
                 .Underline = xlUnderlineStyleNone
                 .ThemeColor = xlThemeColorLight1
                 .TintAndShade = 0
                 .ThemeFont = xlThemeFontNone
            End With
    
            tbl.DataBodyRage.Rows.AutoFit    
            tbl.DataBodyRage.Columns.AutoFit    
            tbl.DataBodyRage.NumberFormat = "0.000"
        Next tbl   
    Next ws

    'MsgBox (Err.Description)  
End Sub