Excel VBA renames table

[simmerer]

Hi,
I have a macro that changes a range into a table. This workbook does not have any tables to begin with. I am creating new functions in the workbook, so I am adding some new sheets created in another workbook, which have tables 2,3, and 4 (Table2, Table3, Table4). When the macro is run I expect it to name the new table as Table1. I have code that runs after, which refers to "Table1" when it creates a formula in a cell.

Alas, excel creates the table, and names it "Table1_1". Rats!

I expect that somewhere in memory, Table1 has some reference that excel sees, so offers up the rename.

I have run this code in test multiple times, closing the workbook without saving. The rename continues to create Table1_1.

Here is the question - Can I count on Table1_1, or will that also become an unseen object reference causing me to have the same issue later with excel using Table1_2 or something else?

I am considering having the code rename the table first, if possible, then continuing. I just am not sure what excel will do with that.

 

[RileyC]

I believe there's a way to see what tables are open in the workbook. If someone doesn't answer in time I'd recommend Googling how to open existing workbook, getting the active sheet, and finding tables that are active on sheet.

 

[igold]

Why not just change the macro that creates the table, to name the table what you want it to be.

Sheet1.ListObjects.Add(xlSrcRange, Range("A1:C30"), , xlYes).Name = "Table1"