4

I am trying to convert a recorded macro to a generic VBA script (my first one ever). The recorded macro produced specific names in the code like Sheet8 and Table5. I need these to be determined dynamically in the code, and so far, I've been able to figure that 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 figuring out how to convert these lines...

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

...to something that uses the myTable ListObject.

Any ideas? Thanks!

pnuts
  • 58,317
  • 11
  • 87
  • 139
user3502992
  • 43
  • 1
  • 3

1 Answers1

0

Try this one:

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 header using it's name, you can do:

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

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

Community
  • 1
  • 1
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80