Code That Creates Names based on the Sheet Name

[tkaram]

Hello,
first time posting here; i tried searching the forum but couldn't find a solution, so thank you for the help:
I am trying to define and create a name for a specific and fixed range of cells in each of my sheets:

example:
Range A1:AK150 of the sheet "Data" to be named "Data"
Range A1:AK150 of the sheet "Beta" to be named "Beta"
...
the same for all the sheets in my workbook.

now obviously i can do it manually by going to Formulas Name Manager, however my workbook has 250 sheets. and it would take me hours to do so, and im sure for one of you guys the code for that could take 5mns maybe

thank you again for the help

cheers

[offthelip]

try this:

[VBA]Dim temp As String


WS_Count = ActiveWorkbook.Worksheets.Count
For i = 1 To WS_Count
ActiveWorkbook.Worksheets(i).Select
temp = Worksheets(i).Name


ActiveWorkbook.Names.Add Name:=temp, RefersTo:=Range(Cells(1, 1), Cells(150, 37))


Next i


[/vba]

[tkaram]

hey, thanks for the quick reply; it seems to be working, however do you know how i could add a letter to the name
so instead of naming the thing Data could i be DataX (sheetname + letter)

[tkaram]

ok i got it:
ActiveWorkbook.Names.Add Name:=temp & "X",
thanks for the quick reply!

[offthelip]

If you are happy with the reply mark the thread as solved so other people don't waste theier time looking at this thread. "Solved" Should be in "thread tools" at the top