hogg
11-10-2008, 01:57 PM
Hey!
I need to define the name of several individual cells over several different sheets. The basic name for each cell is the same but the prefix changes to denote the sheet number, for example:
Sheet 1 cell A1 = s1Description
Sheet 2 cell A1 = s2Description
Sheet 1 cell A2 = s2Type
Sheet 2 cell A2 = s2Type
I've recorded the basic code, but how do I automate the process as there are a lot more cell names to define!
Sheets("TO103").Select
ActiveWorkbook.Names.Add Name:="sh2Description", RefersToR1C1:="=TO103!R5C4"
ActiveWorkbook.Names.Add Name:="sh2Type", RefersToR1C1:="=TO103!R6C4"
ActiveWorkbook.Names.Add Name:="sh2Codes", RefersToR1C1:="=TO103!R7C4"
ActiveWorkbook.Names.Add Name:="sh2Number", RefersToR1C1:="=TO103!R8C4"
Sheets("TO104").Select
ActiveWorkbook.Names.Add Name:="sh3Description", RefersToR1C1:="=TO104!R5C4"
ActiveWorkbook.Names.Add Name:="sh3Type", RefersToR1C1:="=TO104!R6C4"
ActiveWorkbook.Names.Add Name:="sh3Codes", RefersToR1C1:="=TO104!R7C4"
ActiveWorkbook.Names.Add Name:="sh3Number", RefersToR1C1:="=TO104!R8C4"
End Sub
Thanks for your suggestions!
I need to define the name of several individual cells over several different sheets. The basic name for each cell is the same but the prefix changes to denote the sheet number, for example:
Sheet 1 cell A1 = s1Description
Sheet 2 cell A1 = s2Description
Sheet 1 cell A2 = s2Type
Sheet 2 cell A2 = s2Type
I've recorded the basic code, but how do I automate the process as there are a lot more cell names to define!
Sheets("TO103").Select
ActiveWorkbook.Names.Add Name:="sh2Description", RefersToR1C1:="=TO103!R5C4"
ActiveWorkbook.Names.Add Name:="sh2Type", RefersToR1C1:="=TO103!R6C4"
ActiveWorkbook.Names.Add Name:="sh2Codes", RefersToR1C1:="=TO103!R7C4"
ActiveWorkbook.Names.Add Name:="sh2Number", RefersToR1C1:="=TO103!R8C4"
Sheets("TO104").Select
ActiveWorkbook.Names.Add Name:="sh3Description", RefersToR1C1:="=TO104!R5C4"
ActiveWorkbook.Names.Add Name:="sh3Type", RefersToR1C1:="=TO104!R6C4"
ActiveWorkbook.Names.Add Name:="sh3Codes", RefersToR1C1:="=TO104!R7C4"
ActiveWorkbook.Names.Add Name:="sh3Number", RefersToR1C1:="=TO104!R8C4"
End Sub
Thanks for your suggestions!