I have a Macro that fetches a table name and then styles it. However it keeps failing at

Range("Table" & ActiveSheet.Name & "[#All]").Select

How do I select all of the table based on its variable name? The table is called Table22 (as the sheet tab is called 22)

Full code

With Range("A1")

.Parent.ListObjects.Add(xlSrcRange, Range(.End(xlDown), .End(xlToRight)), , xlYes).Name = "Table" & ActiveSheet.Name
Range("Table" & ActiveSheet.Name & "[#All]").Select
ActiveSheet.ListObjects("Table" & ActiveSheet.Name).TableStyle = "TableStyleDark1"

CodePudding user response：

• Avoid using Select
• Use a ListObject variable.
• ListObjects.Add has an optional TableStyleName parameter.

With ActiveSheet
    Dim tbl As ListObject
    Set tbl = .ListObjects.Add(SourceType:=xlSrcRange, _
                  Source:=.Range("A1").CurrentRegion, _
                  XlListObjectHasHeaders:=xlYes, _
                  TableStyleName:="TableStyleDark1")
    tbl.Name = "Table" & .Name
End With