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

1

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

answered Mar 10, 2018 at 19:20

itsLex

7861 gold badge5 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Need to change multiple cell names in Excel using VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related