Define Named Range

[Abdul]

Hi,

For my first 12 worksheets I would like to add named range starting
from column I to Z.

say if my first 12 sheets are named as JAN thru DEC then what I am
looking for is a code to define range name from row 3 to 15 of each
sheet with sheet name and column name.

so for eg. in sheet JAN I3:I5 will be named as JANI and Z3 to Z15 as
JANK. this way for all 12 sheets

How can I do this?

Thanks

 

[Gary Keramidas]

i was kind of in a hurry, so please test this out thoroughly

put the code below in a regular code module

press control - g to see the immediate window if it's not already visible and
execute the macro

when you run this code, it will create ranges for the first 12 sheets, hopefully
your first 12 are jan - dec

then, create a new sub and copy and paste all the immediate window code into
this new module and run it.

hopefully it will create the ranges you want.


Sub name_ranges2() ' this creates the named range for each sheet
Dim nm As Name
Dim i As Long, c As Long, k As Long
k = 3
For i = 1 To 3
For c = 12 To 26
sname = Worksheets(i).Name
Debug.Print "ActiveWorkbook.Names.Add Name:=" & """" & Worksheets(i).Name &
Right(Left(Cells(3, c).Address, 2), 1) & """" & _
", Refersto:=""" & "=" & sname & "!" & Range(Cells(3, c), Cells(15,
c)).Address & """"
Next
k = k + 1
Next
End Sub