Forum Discussion
Changing table name breaks VBA code
Hello,
I have an excel sheet that is used as a template to create additional sheets in an excel workbook.
This sheet has a table named Master_Template.
There is VBA code that references the header names of the table on the template excel sheet.
A few lines of code from this template sheet are:
Dim Module As Range
Dim MACHINE As Range
Set Module = Range("Master_Template[TYPE]")
Set MACHINE = Range("Master_Template[MACHINE]")
All code works without error on the template sheet, but when this template sheet is copied and a new sheet is created, the table name changes from Master_Template to Master_Template1, then Master_Template2, then Master_Template3, etc.
The renaming of this table breaks the VBA code since it is referencing Master_Template.
Is there a way to reference the original table name and ignore the affixed number after the table name each time the template sheet is copied and a new sheet is created to allow the VBA code to work with new sheets?
Thanks in advance.
Does each sheet have only one table? If so, you could use code like this:
Dim Tbl As ListObject Dim Module As Range Dim MACHINE As Range Set Tbl = ActiveSheet.ListObjects(1) Set Module = Tbl.ListColumns("TYPE").DataBodyRange Set MACHINE = Tbl.ListColumns("MACHINE").DataBodyRange
5 Replies
Does each sheet have only one table? If so, you could use code like this:
Dim Tbl As ListObject Dim Module As Range Dim MACHINE As Range Set Tbl = ActiveSheet.ListObjects(1) Set Module = Tbl.ListColumns("TYPE").DataBodyRange Set MACHINE = Tbl.ListColumns("MACHINE").DataBodyRangeHello Hans,
No, there is a second table in the sheets but the table name is not referenced in any code.
Only this Master_Table is referenced by name.
Thanks in advance.
In the Visual Basic Editor, activate the Immediate window (Ctrl+G).
Type
? ActiveSheet.ListObjects(1).Name
and press Enter.
If you see Master_Table, you should be able to use the code as posted by me.
If not, change ListObjects(1) to ListObjects(2) in the code.
