Named Range Macro

[Mashtonsmith1]

Hello,

Help please. I need a macro which will create a named range based on the selected cells in each sheet.

Each sheet is named as a ref number and the cell selection will always start as a year.

So after running the macro it should name the selected cells "REF NO_YEAR". Actually selecting the cells/range will be manual.

My workbook has over 200 tabs so I should end up with around 400 named ranges (for 2022 and 2023)

I wont be able to run this across the entre work book as the loaction of the data I want isn't in a consistent position - I'll need to do it for each sheet using this macro.

Many thanks

 

[Micron]

Based on the info provided, perhaps something like this in a standard module:

Sub AddRange()
Dim rng As Range
Dim nme As Name

Set rng = Application.InputBox("Select Range", Type:=8)
ThisWorkbook.Names.Add Name:="REF_NO_YEAR", RefersTo:=rng

End Sub

That would create a named range at the workbook level (not the sheet) which is what I interpret that you want. It won't check if the named range already exists so it will take on whatever range you select for that name. BTW, AFAIK, named ranges cannot contain spaces so it's not exactly what you posted for the name.