Formatting table in worksheet

[JasonG]

Hi,

Each I have to format my new excel file containing from 10 to 30 worksheets. At this time I have to go each worksheet one by one to format.
Here is a macro what I did in one worksheet7 named "Jason Data":

Sheets("Jason Data").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$C$3"), , xlYes).Name = "Table7"
Range("Table7[#All]").Select
ActiveSheet.ListObjects("Table7").TableStyle = "TableStyleMedium9"

How can I do it by looping all the worksheets and format them in the way above. Starting range is always A1 but ending range will change on each worksheet. Can we use a variable for "Table7"?
In other words, will ActiveSheet.ListObjects(myvariable).TableStyle = "TableStyleMedium9" work?

Best,

[SamT]

Table range = Range("A1").CurrentRegion

For i = 1 to sheets.count 
TableName = "Table" & Cstr(i)

ActiveSheet.ListObjects(TableName).TableStyle =...
Next i

[JasonG]

I have tried it but getting this error --> Run-time error '9': Subscript out of range at Activesheet.........


Dim TableName As String
Dim i As Integer

For i = 1 To Sheets.Count
TableName = "Table" & CStr(i)
Sheets(i).Select
ActiveSheet.ListObjects(TableName).TableStyle = "TableStyleMedium9"
Next i

Not sure what's wrong?

[SamT]

Aren't you supposed to add and name a listobject? You do in your first post

[Paul_Hossler]

I'd keep it simple

This loops all worksheets and then loops all tables on the worksheet

Option Explicit

Sub FormatAllTables()

    Dim ws As Worksheet
    Dim lo As ListObject

    Application.ScreenUpdating = False
    
    For Each ws In ActiveWorkbook.Worksheets
        For Each lo In ws.ListObjects
            lo.TableStyle = "TableStyleMedium9"
        Next
    Next
    
    Application.ScreenUpdating = True
End Sub