Changing table name breaks VBA code

[chowell97]

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.

[#2 · Accepted solution]

@chowell97 

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

[#3]

@Hans Vogelaar 

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. 

[#4]

Thank you for your help, your suggestion worked as intended!

[#5]

@chowell97 

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.

[#6]

Thank you for the additional tip!