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?
Thx!
Sub Macro1()
Sheets("SN A").Select
Range("E10").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
Sheets(SheetName).Select
Range("E10").Select
ActiveWorkbook.Worksheets(SheetName).Names.Add Name:="new_name_1", RefersToR1C1:="='SheetName'!R10C5"
ActiveWorkbook.Worksheets(SheetName).Names("new_name_1").Comment = ""
End Sub