Forum Discussion
Solved
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 ...
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
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
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.
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.
- Thank you for the additional tip!
- Thank you for your help, your suggestion worked as intended!