Feb 25 2023 11:42 PM
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.
Feb 26 2023 03:16 AM
SolutionDoes 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
Feb 26 2023 05:39 AM
Hello 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.
Feb 26 2023 06:03 AM
Feb 26 2023 06:04 AM
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.
Feb 26 2023 03:16 AM
SolutionDoes 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