How to get the name of a table in a variable?

[TheBlueMusketeer]

Hello,

Any ideas on how to capture the name of a table in a variable?

To add a table, we use the following code (recorded it) :
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$D$4:$J$16"), , xlNo).Name = "Table1"

I need to refer to a table from a sheet that gets updated periodically but sometimes the same table download ends with a different number - "Table2" for example, and my vlookup formula breaks. Therefore I want to use a variable that stores the table name from this particular sheet every time the macro is run.

Thanks,
TBM
Excel 2007

 

[Peter_SSs]

Is there always one and only one table on the sheet? Try

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ReferToTable_v1()<br>  <SPAN style="color:#00007F">Dim</SPAN> TableName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>  TableName = Sheets("Table Sheet").ListObjects(1).Name<br>  MsgBox TableName<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


If there could be more than one table but you know where the table of interest is (J1 in my example) then try

<font face=Courier New><br><br><SPAN style="color:#00007F">Sub</SPAN> ReferToTable_v2()<br>  <SPAN style="color:#00007F">Dim</SPAN> TableName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>  <br>  TableName = Sheets("Table Sheet").Range("J1").ListObject.Name<br>  MsgBox TableName<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[TheBlueMusketeer]

Thanks a bunch Peter! It worked. There is only one table in my sheet but now I know what to do when there are more. Thanks again!

Regards,
TBM