Want to install local cell names on multiple sheets in a workbook

  • I’m using Excel ???? and have a workbook with a number of identically laid out sheets. I’m trying to write a macro to step thru the sheets and, on each to select a cell (same on each sheet) and give it a local name (same name on each sheet) associated only with the sheet selected. I copied Macro1 to do this for one sheet and the macro works. But I haven’t been able to revise the macro (to Macro2) to use the sheet index to step thru all the sheets. Below is Macro1 which works and Macro2 (addresses only sheet #1 for a test) – doesn’t work. The name of sheet1 is “SN A”. I’ve tried a number of iterations with different named variable, use of quotes, setting Dim as String, etc. but haven’t found anything that works. Anyone have an idea?


    Sub Macro1()

    Sheets("SN A").Select


    ActiveWorkbook.Worksheets("SN A").Names.Add Name:="new_name_1", RefersToR1C1:="='SN A'!R10C5"

    ActiveWorkbook.Worksheets("SN A").Names("new_name_1").Comment = ""

    End Sub

    Sub Macro2()

    Z = 1

    SheetName = Sheets(Z).Name



    ActiveWorkbook.Worksheets(SheetName).Names.Add Name:="new_name_1", RefersToR1C1:="='SheetName'!R10C5"

    ActiveWorkbook.Worksheets(SheetName).Names("new_name_1").Comment = ""

    End Sub

  • Maybe try :

    Sub Macro2()
      Dim wSht As Worksheet
      For Each wSht In ThisWorkbook.Sheets
        With wSht
          .Names.Add Name:="new_name_1", RefersToR1C1:="='" & wSht.Name & "'!R10C5"
          .Names("new_name_1").Comment = ""
        End With
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!