Change Code to Match Tab Name? Is this an okay idea?

[Walter]

shtSup1.Names.Add Name:="Sup1_BCAPA_Ratings",
RefersTo:=Range("Arrow!$C$6,Arrow!$C$16,Arrow!$C$23,Arrow!$C$36,Arrow!$C$56"), Visible:=True

I have many lines of code that looks similar to the above line and I have
built a workbook template that I copy over to create a new tool for the end
user. Each time most of the worksheet names change and then I have to go
into the code and update the ranges to match.
My question is if I change the worksheet (tab) names can the code change to
reflect those changes. For example, rather than have Arrow in the above code
can there be a reference to the worksheet name so that anytime the workbook
sheet names are updated then the code changes accordingly. Seems to me this
would save time in updating the code each time the workbook is copied over.
Can I then protect worksheet names so other people cannot randomly change the
names?

Or is this a bad idea to code in this way?

 

[Barb Reinhardt]

If I were you, I'd define a static worksheet code name for the worksheet
Arrow so that you can change the worksheet name. If you view source for the
worksheet in question, you can change the Name in the properties for the
sheet.

Your code will get considerably more complicated, but it can be done like
this. Let's say the worksheet CODENAME is now Arrow. To get this piece of
it, you'd need to do this

Arrow!$C$6
Arrow.codename & "!$C$6"

If you need more help, come back.

HTH,
Barb Reinhardt

 

[Luke M]

Each sheet has a hard code value (visible in the VBA navigation box. Assuming
Sheet named "Arrow" is actually Sheet1, should be able to change this to:

shtSup1.Names.Add Name:="Sup1_BCAPA_Ratings",
RefersTo:=Range(Sheet1.Name & "!$C$6," & Sheet1.Name & "!$C$16," &
Sheet1.Name & "!$C$23," & Sheet1.Name & "!$C$36," & Sheet1.Name & "!$C$56"),
Visible:=True

 

[Barb Reinhardt]

Correction. If the worksheet codename is Arrow, you'd need to do this

"'" & Arrow.name & "'!$C$6"

I always put single quotes around the name becuase I never know if the name
will have spaces or not.

 

[Dave Peterson]

Is arrow the name of the shtSup1 sheet?

if yes:

set shtsup1 = worksheets("Arrow")
with shtsup1
.range("c6,c16,c23,c36,c56").name = "'" & .name & "'!Supl_BCAPA_Ratings"
end with