PDA

View Full Version : [SOLVED] Code That Creates Names based on the Sheet Name



tkaram
03-15-2017, 12:54 PM
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
03-15-2017, 01:15 PM
try this:

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

tkaram
03-15-2017, 02:02 PM
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
03-15-2017, 02:16 PM
ok i got it:
ActiveWorkbook.Names.Add Name:=temp & "X",
thanks for the quick reply!

offthelip
03-15-2017, 02:21 PM
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