Changing names of named ranges using vba

[Brijesh]

Dear All

I have a sheet "GSVPerc" in an excel workbook. In this sheet there are 50 tables whose names are defined as "GSVTable1", "GSVTable2", "GSVTable3",.........., "GSVTable50".
Now I have created a copy of this sheet in the same workbook. I have renamed this new sheet as "SSVPerc".
Now this sheet "SSVPerc" also have 50 tables with same names as of the tables on sheet "GSVPerc". But the tables on new sheet "SSVPerc" don't have the scope as workbook. Rather they have the scope of that local sheet "SSVPerc". I have to change the name of these 50 tables (on sheet "SSVPerc"). The new names to be given to these tables are "SSVTable1", "SSVTable2", "SSVTable3",.........., "SSVTable50".

I am using following code for this purpose:

Sub NameChange ()

For i = 1 to 50

With sheets("SSVPerc")
.Range("GSVTable" & i).Name= "SSVTable" & i
End With

Next i

End Sub


When I run code it run successfully without any error but it does nothing. No name change take place. I have run this type of code earlier once in some other instance and at that time code was working fine. But this time the code is doing nothing.

Further I have no idea for coding to change the scope of Named Ranges.

Please suggest.

Regards

Brijesh

 

[PeterB]

Hi Brijesh,

In VBA, tables are a specific type of object, called listobject. To change the name of all table on your worksheet please use:

Sub NameChange ()

dim i as long
dim tbl as listobject
dim ws as worksheet

set ws = thisworkbook.sheets("SSVPerc")

for each tbl in ws.listobjects
tbl.Name= "SSVTable" & i
Next tbl

'Clean up
set tbl = nothing
set ws = nothing

End Sub

Regards,

Peter

 

[Brijesh]

Thanks Peter

I'll apply this code to my workbook.

In between I want to ask one more question.

As alredy clear from my above thread that I have a sheet named "GSVPerc" with 50 tables. I have to create another sheet with name "SSVPerc" with 50 tables with same format, headings and orientation but with slight changes in name of tables as mentioned earlier (for which you have provided the code). The problem is that when I create a copy of sheet "GSVPerc" and rename it to "SSVPerc", the tables on the new sheet "SSVPerc" have the same name as of those tables on sheet "GSVPerc" and scope of these new tables is the local worksheet in spite of whole workbook. To change the scope of these tables to whole workbook, I have to delete Names of these tables and then I have to reassign name to all 50 table again ("SSVTable1", "SSVTable2", "SSVTable3", ........, "SSVTable50") which is a tedious work. Is there any other way to do this?

 

[PeterB]

Hi Brijesh,

Please can you upload your workbook showing this problem. I would expect the tables to be created with a named range which is set at workbook level.

Regards,

Peter

 

[SirJB7]

Hi, Brijesh!
What Excel version are you using? I created a new workbook, entered data in 3x2 cell range, selected it and pressed Ctrl-T, and got the 1st table. Workbook scope as all tables (don't confuse with named ranges). Copied that worksheet (no warning message displayed as it'd happen with named ranges with workbook scope), went to Name Manager and there were 2 tables with the proper scope (workbook, I insist) and of course with different names.
However, please repeat this procedure and check what happens.
Regards!