How to improve my code!?

[hogg]

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!

[VBA]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[/VBA]


Thanks for your suggestions!

[Bob Phillips]

[vba]

Sub Addnames()

Call AddNamesPerSheet(2, "TO103")
Call AddNamesPerSheet(3, "TO104")

End Sub

Sub AddNamesPerSheet(ByVal Num As Long, ByVal SheetName As String)

With ActiveWorkbook.Names

.Add Name:="sh" & Num & "Description", RefersToR1C1:="=" & SheetName & "!R5C4"
.Add Name:="sh" & Num & "Type", RefersToR1C1:="=" & SheetName & "!R6C4"
.Add Name:="sh" & Num & "Codes", RefersToR1C1:="=" & SheetName & "!R7C4"
.Add Name:="sh" & Num & "Number", RefersToR1C1:="=" & SheetName & "!R8C4"
End With
End Sub
[/vba]

[Dr.K]

XLD did a find job there. For more info on writing better code, see this article:

http://vbaexpress.com/forum/showthread.php?t=9882

[hogg]

Thanks XLD for the code - works perfectly!

Thanks Dr.K for the pointer to the article - looks like some useful stuff!

Do I mark this thread as "solved" now? If so how!?

[GTO]

Greetings,

Thanks for showing the interest in marking Solved too! Makes life much easier for those following a thread to help answer :-) Anyways, as you are the Original Poster (OP), when you open the thread, above your first post, there's a little section called Thread Tools. Click there, and select Solved.

Mark

[hogg]

Mark,

I've logged in a few times over the last few days and still the option to select solve still doesn't appear in the thread tools.

I have three options:
Show printable version
Email this page
Unsubscribe from this thread

Sorry for being an idiot but I can't find it!

[lucas]

hogg, does this thread have anything to do with your thread in the Word forum?

[hogg]

This thread relates to the thread in the word forum - If I'd known what I was doing at the start I'd only have the one! Sorry chaps!

http://www.vbaexpress.com/forum/showthread.php?t=23468