Select Table range with variable

Question

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"

[Avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to begin with. And work with `ListObject`s, which is what a table is. — BigBen, Jan 27 '22 at 15:29
`Dim tbl As ListObject`, `Set tbl = .Parent.ListObjects.Add(xlSrcRange, Range(.End(xlDown), .End(xlToRight)), , xlYes)`. Then you can modify `tbl` to your heart's content, including changing its `.Name` and its `.TableStyle`. — BigBen, Jan 27 '22 at 15:38

score 3 · Accepted Answer · answered Jan 27 '22 at 15:54

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

Select Table range with variable

1 Answers1