All,

I am creating a template model. I would codename (NOT SHEET NAME) 15
different sheets as Entity1, Entity2,.......Entity15.

Is there a way I can convert a string into a codename. e.g.

Dim MySheet as Worksheet, n as integer

for n = 1 to 15
MySheet = "Entity" & n
MySheet.activate
run Code
next

This code does not work???

Any help appreciated.

RK

Try this. Also can name at the same time. NO selections necessary

Sub ChangeCodeNameandSheetName()
For i = 1 To Sheets.Count
ThisWorkbook.VBProject.VBComponents(Sheets(i).Code Name) _
.Properties("_CodeName").Value = "Entity" & i
Sheets(i).Name = "RK" & i
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"rk0909" wrote in message
...
All,

I am creating a template model. I would codename (NOT SHEET NAME) 15
different sheets as Entity1, Entity2,.......Entity15.

Is there a way I can convert a string into a codename. e.g.

Dim MySheet as Worksheet, n as integer

for n = 1 to 15
MySheet = "Entity" & n
MySheet.activate
run Code
next

This code does not work???

Any help appreciated.

RK

Try this...

Dim MySheet as Worksheet, n as long

for n = 1 to 15
with thisworkbook
Set MySheet = .Worksheets(.VBProject.VBComponents("Entity" &
n).Properties("Name").Value)
end with
MySheet.activate
run Code
next n
--
HTH...

Jim Thomlinson


"rk0909" wrote:

All,

I am creating a template model. I would codename (NOT SHEET NAME) 15
different sheets as Entity1, Entity2,.......Entity15.

Is there a way I can convert a string into a codename. e.g.

Dim MySheet as Worksheet, n as integer

for n = 1 to 15
MySheet = "Entity" & n
MySheet.activate
run Code
next

This code does not work???

Any help appreciated.

RK

Jim,

This is exactly what I was looking for.

Thanks much,

RK

"Jim Thomlinson" wrote:

Try this...

Dim MySheet as Worksheet, n as long

for n = 1 to 15
with thisworkbook
Set MySheet = .Worksheets(.VBProject.VBComponents("Entity" &
n).Properties("Name").Value)
end with
MySheet.activate
run Code
next n
--
HTH...

Jim Thomlinson


"rk0909" wrote:

All,

I am creating a template model. I would codename (NOT SHEET NAME) 15
different sheets as Entity1, Entity2,.......Entity15.

Is there a way I can convert a string into a codename. e.g.

Dim MySheet as Worksheet, n as integer

for n = 1 to 15
MySheet = "Entity" & n
MySheet.activate
run Code
next

This code does not work???

Any help appreciated.

RK

I guess I misunderstood. This gives the sheets the codenames

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Try this. Also can name at the same time. NO selections necessary

Sub ChangeCodeNameandSheetName()
For i = 1 To Sheets.Count
ThisWorkbook.VBProject.VBComponents(Sheets(i).Code Name) _
.Properties("_CodeName").Value = "Entity" & i
Sheets(i).Name = "RK" & i
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"rk0909" wrote in message
...
All,

I am creating a template model. I would codename (NOT SHEET NAME) 15
different sheets as Entity1, Entity2,.......Entity15.

Is there a way I can convert a string into a codename. e.g.

Dim MySheet as Worksheet, n as integer

for n = 1 to 15
MySheet = "Entity" & n
MySheet.activate
run Code
next

This code does not work???

Any help appreciated.

RK