Need to change multiple cell names in Excel using VBA

Question

I have multiple cell names already created in excel. I want to add prefix to each of these names based on what sheet they are in. I'm currently using this VBA code to do so:

 Sub RenameCells() Dim n As Name For Each n In Sheet1.Names n.Name = "NLOC1_" & n.Name Next n End Sub

However, it does not seem to actually change the names in the sheet. I get no errors so I'm not sure what is wrong and why nothing is changing.

That is probably because the name is a workbook scope and not a worksheet scope. — Scott Craner
– Scott Craner, Commented Mar 10, 2018 at 17:40

itsLex · Accepted Answer · 2018-03-10 19:20:11Z

The names seem to be at Workbook level, so you need to use:

Sub RenameCells() Dim n As Name For Each n In ActiveWorkbook.Names n.Name = n.RefersToRange.Worksheet.Name & "_" & n.Name Next n End Sub

Collectives™ on Stack Overflow

Need to change multiple cell names in Excel using VBA

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related