In VBA I know that I can refer to a structured table via this way:
Set Tbl = Sheets("MySheetName").ListObjects("MyTblName")
Then do Tbl.XXX where .XXX can be .Name, .Range, etc.
However, I want to refer to this table without referring to the Sheet name, so that the macro does not break if the sheet name changes.
Is this possible?
After some research I found something that is not a perfect solution. You can make use of the Range function in VBA like this:
Set tbl = Range("TableName[#All]")
However this is not a ListObject but a Range. You can also do other references like:
the body of the structured table (excluding headers)
Range("TableName")
Column called "MyColumn" of the body
Range("TableName[MyColumn]")
etc.
Then you call something like: tbl.ListObject to refer to the structured table where the range is found.
The cool thing is that Range() will always work on the ActiveWorkbook, so you can be in WorkBook B and open a macro in Workbook A and it will still run on Workbook B
Source: https://peltiertech.com/structured-referencing-excel-tables/
If your table is on the ActiveSheet, you can use it like:
Set Tbl = ActiveSheet.ListObjects("MyTblName")