How to use record [[#Headers], [Column]] with table ListObject instead of "TableName"?

Question

I am trying to convert a recorded macro to a generic VBA script (my first). The recorded macro created specific names in code like Sheet8 and Table5. I need them to be defined dynamically in the code, and so far I could figure it out ...

'This table name needs to be dynamically determined...
Dim myTable As ListObject
Set myTable = ActiveSheet.ListObjects.Add(xlSrcRange, myRng, , xlNo)

However, I need help on how to convert these strings ...

Range("Table5[[#Headers],[Column1]]").Select

... to what uses myTable ListObject.

Any ideas? Thank!

Answer 1

Try the following:

Dim myTable As ListObject
Dim myRng As Range, rngH1 As Range
'where to place the table
Set myRng = ThisWorkbook.Worksheets("Sheet1").Range("D5:H10")
'create table
Set myTable = ActiveSheet.ListObjects.Add(xlSrcRange, myRng, , xlNo)
'get firts header
Set rngH1 = myTable.HeaderRowRange.Columns(1)
'select first header (assuming that Sheet where table placed is active)
rngH1.Select

If you want to find the title using its name, you can do:

'select header "Column3" (assuming that Sheet where table placed is active)
myTable.ListColumns("Column3").Range(1).Select

may also be interesting: how to avoid using Select / Active statements