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 to begin with. And work with ListObjects, which is what a table is. — BigBen
– BigBen, Commented Jan 27, 2022 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
– BigBen, Commented Jan 27, 2022 at 15:38

BigBen · Accepted Answer · 2022-01-27 15:54:39Z

3

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

answered Jan 27, 2022 at 15:54

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select Table range with variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related