Is there any way to make table names local?

[JenniferMurphy]

I often need to have multiple copies of a sheet, such as for different years. But when I make a copy of a sheet containing a table, Excel renames the table by adding a number. This is because Excel makes all table names global. If the name I gave to the table in the first sheet was "TableRatings", the name in the new sheet might be "TableRatings4" and in the next copy of the sheet, it might be "TableRatings45".

This causes me some problems. If I need to add a new column that will contain any references to data in the table, I am never sure which table is in this sheet.

This does not happen with local names.

I wish Excel allowed me to decide whether the table name should be local or global. Is there any way to do that?

 

[Alex Blakenburg]

I don't know of any way to make the Table Name have a scope of Local.
I am not sure it much different in concept to copying a sheet though. When you copy a sheet it adds a numeric suffix to make it unique and you change the name. Similarly for the TableName when you copy the sheet you would also change the table name to make it identifiable as belonging to the Sheet.
Any formulas on the copied sheet pointing to that table will automatically change when you change the table name.

If helps at all you can put the TableName box into your quick access toolbar (but it really only saves you about 1 click).

PS: I see that you are prefixing the name. My prefix is lower case "tbl" it makes it less distracting from the unique part of the name while still helping access just the table names by typing in tbl and in sorting the Named Ranges in some logical grouping.

 

[JenniferMurphy]

I don't know of any way to make the Table Name have a scope of Local.

That's what I was afraid of.

I am not sure it much different in concept to copying a sheet though. When you copy a sheet it adds a numeric suffix to make it unique and you change the name. Similarly for the TableName when you copy the sheet you would also change the table name to make it identifiable as belonging to the Sheet.
Any formulas on the copied sheet pointing to that table will automatically change when you change the table name.

I see it differently. If I have a sheet for each year, for example, I might name them 2020, 2021, 2022, etc. I suppose I could rename the tables in a similar manner (tbl2020, tbl2021, tbl2022), but I don't have to do that with anything else on that sheet. I usually make almost all named ranges local. That's the default for variable names in well-written subroutines. Then all of the expressions on each sheet look exactly the same.

IMNSHO, m$ft made a very clueless error in making the default for all named ranges global and, especially, for not allowing me to make table names local.

ymmv

PS: I see that you are prefixing the name. My prefix is lower case "tbl" it makes it less distracting from the unique part of the name while still helping access just the table names by typing in tbl and in sorting the Named Ranges in some logical grouping.

That's an excellent suggestion. I do that for VBA variables. Not sure why I didn't think of it here. Thanks

 

[Alex Blakenburg]

On the flip side by having it as a Workbook scope:
• You can use Goto or the Address Drop down from any sheet to get to the table
• If you are using the table in another sheet in a formula or Pivot or Chart reference it keeps the referencing to a managable length (some people have very long sheet names)
• If you are using the table in another sheet you simply have to start typing and if you have a prefix of say tbl "=tbl" will get you a list of the available tables.
• Its easier to refer to the table in code

 

[My Aswer Is This]

Why not write any code you want to refer to the Table Number like this:

Sub Table_Range()
'Modified 10/15/2022  5:14:07 AM  EDT

With ActiveSheet.ListObjects(1).DataBodyRange
.Cells(1, 1).Value = "Alpha"
.Cells(2, 1).Value = "Bravo"
End With
End Sub

 

[JenniferMurphy]

On the flip side by having it as a Workbook scope:
• You can use Goto or the Address Drop down from any sheet to get to the table
• If you are using the table in another sheet in a formula or Pivot or Chart reference it keeps the referencing to a managable length (some people have very long sheet names)
• If you are using the table in another sheet you simply have to start typing and if you have a prefix of say tbl "=tbl" will get you a list of the available tables.
• Its easier to refer to the table in code

All of that and more would be possible if I could choose whether to make them local or global. Forcing all table names to be global is, frankly, stupid and a violation of good coding practices.

 

[JenniferMurphy]

Why not write any code you want to refer to the Table Number like this:

Sub Table_Range()
'Modified 10/15/2022  5:14:07 AM  EDT

With ActiveSheet.ListObjects(1).DataBodyRange
.Cells(1, 1).Value = "Alpha"
.Cells(2, 1).Value = "Bravo"
End With
End Sub

I don't understand this. I am not referencing the table in VBA, justl expressions.

 

[My Aswer Is This]

I don't understand this. I am not referencing the table in VBA, justl expressions.

justl expressions. ???

What are expressions?

 

[MARK858]

What are expressions?

Some people use the term expression rather than formula, in old terms a formula was the syntax and an expression was the execution but these days some interchange the terms.

You'll find the term used a lot if you do algebra, an expression is just an equation that uses data and operators to return a result.

 

[My Aswer Is This]

Some people use the term expression rather than formula, in old terms a formula was the syntax and an expression was the execution but these days some interchange the terms.

You'll find the term used a lot if you do algebra, an expression is just an equation that uses data and operators to return a result.

OK Thanks for that explanation