string to codename
 

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

string to codename
 

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

string to codename
 

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

string to codename
 

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

string to codename
 

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