PDA

View Full Version : Formatting table in worksheet



JasonG
01-19-2018, 10:49 AM
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
01-19-2018, 01:17 PM
Table range = Range("A1").CurrentRegion


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

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

JasonG
01-19-2018, 08:57 PM
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
01-20-2018, 11:09 AM
Aren't you supposed to add and name a listobject? You do in your first post

Paul_Hossler
01-20-2018, 12:19 PM
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