i want to loop through 12 sheets, codename sheet1 thru sheet12

why doesn't this work?

i = 1
For i = 1 To 12
cname = "Sheet" & i
cname.Select
Next

--


Gary

i ended up using index numbers instead
Worksheets(i).Activate

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to loop through 12 sheets, codename sheet1 thru sheet12

why doesn't this work?

i = 1
For i = 1 To 12
cname = "Sheet" & i
cname.Select
Next

--


Gary

Try

dim cname as Worksheet
For i = 1 To 12
set cname = sheets("Sheet" & i)
cname.Select
Next



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to loop through 12 sheets, codename sheet1 thru sheet12

why doesn't this work?

i = 1
For i = 1 To 12
cname = "Sheet" & i
cname.Select
Next

--


Gary

thanks carlos

--


Gary


"Carlos" wrote in message
...
Try

dim cname as Worksheet
For i = 1 To 12
set cname = sheets("Sheet" & i)
cname.Select
Next



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to loop through 12 sheets, codename sheet1 thru sheet12

why doesn't this work?

i = 1
For i = 1 To 12
cname = "Sheet" & i
cname.Select
Next

--


Gary

i think that only works for the sheet name, not the codename

--


Gary


"Carlos" wrote in message
...
Try

dim cname as Worksheet
For i = 1 To 12
set cname = sheets("Sheet" & i)
cname.Select
Next



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to loop through 12 sheets, codename sheet1 thru sheet12

why doesn't this work?

i = 1
For i = 1 To 12
cname = "Sheet" & i
cname.Select
Next

--


Gary

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to loop through 12 sheets, codename sheet1 thru sheet12

why doesn't this work?

i = 1
For i = 1 To 12
cname = "Sheet" & i
cname.Select
Next

--


Gary

This works:

---------------------------
Sub Button4_Click()
Dim i As Integer
For i = 1 To 4
Sheets("Sheet" & i).Activate
MsgBox ActiveSheet.Name
Next
End Sub
-------------------------

Ciao
Bruno

Sub RenameSheets()
Dim sSheet As String
Dim oVBMod As Object
Dim i As Long

With ActiveWorkbook.VBProject
For Each oVBMod In .VBComponents
Select Case oVBMod.Type
Case 100:
If oVBMod.Name < "ThisWorkbook" Then
sSheet =
CStr(.VBComponents(oVBMod.Properties("Codename")). Properties("Name"))
With Worksheets(sSheet)
i = i + 1
.Parent.VBProject.VBComponents(.CodeName) _
.Properties("_CodeName") = "Sheet" & i
'For i = 1 To oVBMod.Properties.Count
' Debug.Print oVBMod.Properties(i).Name
'Next i
End With
End If
End Select
Next oVBMod
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to loop through 12 sheets, codename sheet1 thru sheet12

why doesn't this work?

i = 1
For i = 1 To 12
cname = "Sheet" & i
cname.Select
Next

--


Gary

That is correct Gary.

Although changeable, the usage for codename assumes it isn't

The alternative (to support variability or to get a list) is to loop through
the objects in the project and pick up the codename property.

See Chip Pearson's code on programming the VBE for insights
http://www.cpearson.com/excel/vbe.htm

and codenames specifically:
http://www.cpearson.com/excel/codemods.htm

although if you are populating a combobox with sheetnames

for each sh in Worksheets
.AddItem sh.name
Next


--
Regards,
Tom Ogilvy

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i think that only works for the sheet name, not the codename

--


Gary


"Carlos" wrote in message
...
Try

dim cname as Worksheet
For i = 1 To 12
set cname = sheets("Sheet" & i)
cname.Select
Next



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to loop through 12 sheets, codename sheet1 thru sheet12

why doesn't this work?

i = 1
For i = 1 To 12
cname = "Sheet" & i
cname.Select
Next

--


Gary

thanks for your help on this, bob

--


Gary


"Bob Phillips" wrote in message
...
Sub RenameSheets()
Dim sSheet As String
Dim oVBMod As Object
Dim i As Long

With ActiveWorkbook.VBProject
For Each oVBMod In .VBComponents
Select Case oVBMod.Type
Case 100:
If oVBMod.Name < "ThisWorkbook" Then
sSheet =
CStr(.VBComponents(oVBMod.Properties("Codename")). Properties("Name"))
With Worksheets(sSheet)
i = i + 1
.Parent.VBProject.VBComponents(.CodeName) _
.Properties("_CodeName") = "Sheet" & i
'For i = 1 To oVBMod.Properties.Count
' Debug.Print oVBMod.Properties(i).Name
'Next i
End With
End If
End Select
Next oVBMod
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to loop through 12 sheets, codename sheet1 thru sheet12

why doesn't this work?

i = 1
For i = 1 To 12
cname = "Sheet" & i
cname.Select
Next

--


Gary

This code seems like your going in circles.

If you already have a reference to the component (sheet), then why use it to
find the sheet tab name to use to get a reference to the component which you
already have?

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...
Sub RenameSheets()
Dim sSheet As String
Dim oVBMod As Object
Dim i As Long

With ActiveWorkbook.VBProject
For Each oVBMod In .VBComponents
Select Case oVBMod.Type
Case 100:
If oVBMod.Name < "ThisWorkbook" Then
sSheet =
CStr(.VBComponents(oVBMod.Properties("Codename")). Properties("Name"))
With Worksheets(sSheet)
i = i + 1
.Parent.VBProject.VBComponents(.CodeName) _
.Properties("_CodeName") = "Sheet" & i
'For i = 1 To oVBMod.Properties.Count
' Debug.Print oVBMod.Properties(i).Name
'Next i
End With
End If
End Select
Next oVBMod
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to loop through 12 sheets, codename sheet1 thru sheet12

why doesn't this work?

i = 1
For i = 1 To 12
cname = "Sheet" & i
cname.Select
Next

--


Gary

thanks for the tip.

--


Gary


"Tom Ogilvy" wrote in message
...
That is correct Gary.

Although changeable, the usage for codename assumes it isn't

The alternative (to support variability or to get a list) is to loop
through
the objects in the project and pick up the codename property.

See Chip Pearson's code on programming the VBE for insights
http://www.cpearson.com/excel/vbe.htm

and codenames specifically:
http://www.cpearson.com/excel/codemods.htm

although if you are populating a combobox with sheetnames

for each sh in Worksheets
.AddItem sh.name
Next


--
Regards,
Tom Ogilvy

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i think that only works for the sheet name, not the codename

--


Gary


"Carlos" wrote in message
...
Try

dim cname as Worksheet
For i = 1 To 12
set cname = sheets("Sheet" & i)
cname.Select
Next



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to loop through 12 sheets, codename sheet1 thru sheet12

why doesn't this work?

i = 1
For i = 1 To 12
cname = "Sheet" & i
cname.Select
Next

--


Gary

If you really need the CodeName, not the Sheet name, try
something like

Dim N As Integer
For N = 1 To 3
Debug.Print ThisWorkbook.VBProject.VBComponents("Sheet" &
N).Name
Next N




"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to loop through 12 sheets, codename sheet1 thru sheet12

why doesn't this work?

i = 1
For i = 1 To 12
cname = "Sheet" & i
cname.Select
Next

--


Gary

chip:

what i was trying to do was loop all sheets based on the code name. the
sheet names were all changed, but i knew the code names were sheet1 thru
sheet 12. i was trying to concatenate the number onto the end of the word
sheet so i could loop.
i used index number instead, the index numbers are 2 thru 13. just wanted to
use code names in case any sheets were inserted.

i used the index (i) variable because i could not figure out how to select
the sheet(i) by code name.

For i = 2 To 13
Worksheets(i).Activate
ActiveSheet.Unprotect
Range("b4:i56,k4:p56,r4:t56,v4:af56").ClearContent s
ActiveSheet.Protect
Range("a1").Select
Next

--


Gary


"Chip Pearson" wrote in message
...
If you really need the CodeName, not the Sheet name, try something like

Dim N As Integer
For N = 1 To 3
Debug.Print ThisWorkbook.VBProject.VBComponents("Sheet" & N).Name
Next N




"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to loop through 12 sheets, codename sheet1 thru sheet12

why doesn't this work?

i = 1
For i = 1 To 12
cname = "Sheet" & i
cname.Select
Next

--


Gary

If the code names are sheet2 to sheet13 then

Sub ABC()
Dim i As Long, sh As Object
Dim sStr As String
For i = 2 To 13
sStr = ThisWorkbook.VBProject _
.VBComponents("Sheet" & i) _
.Properties("Name").Value
MsgBox "Tab name of Sheet" & i & _
" is " & sStr
Next

End Sub

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
chip:

what i was trying to do was loop all sheets based on the code name. the
sheet names were all changed, but i knew the code names were sheet1 thru
sheet 12. i was trying to concatenate the number onto the end of the word
sheet so i could loop.
i used index number instead, the index numbers are 2 thru 13. just wanted
to
use code names in case any sheets were inserted.

i used the index (i) variable because i could not figure out how to select
the sheet(i) by code name.

For i = 2 To 13
Worksheets(i).Activate
ActiveSheet.Unprotect
Range("b4:i56,k4:p56,r4:t56,v4:af56").ClearContent s
ActiveSheet.Protect
Range("a1").Select
Next

--


Gary


"Chip Pearson" wrote in message
...
If you really need the CodeName, not the Sheet name, try something like

Dim N As Integer
For N = 1 To 3
Debug.Print ThisWorkbook.VBProject.VBComponents("Sheet" & N).Name
Next N




"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to loop through 12 sheets, codename sheet1 thru sheet12

why doesn't this work?

i = 1
For i = 1 To 12
cname = "Sheet" & i
cname.Select
Next

--


Gary

ok, thanks for that, tom. i'll file it away

--


Gary


"Tom Ogilvy" wrote in message
...
If the code names are sheet2 to sheet13 then

Sub ABC()
Dim i As Long, sh As Object
Dim sStr As String
For i = 2 To 13
sStr = ThisWorkbook.VBProject _
.VBComponents("Sheet" & i) _
.Properties("Name").Value
MsgBox "Tab name of Sheet" & i & _
" is " & sStr
Next

End Sub

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
chip:

what i was trying to do was loop all sheets based on the code name. the
sheet names were all changed, but i knew the code names were sheet1 thru
sheet 12. i was trying to concatenate the number onto the end of the word
sheet so i could loop.
i used index number instead, the index numbers are 2 thru 13. just wanted
to
use code names in case any sheets were inserted.

i used the index (i) variable because i could not figure out how to
select
the sheet(i) by code name.

For i = 2 To 13
Worksheets(i).Activate
ActiveSheet.Unprotect
Range("b4:i56,k4:p56,r4:t56,v4:af56").ClearContent s
ActiveSheet.Protect
Range("a1").Select
Next

--


Gary


"Chip Pearson" wrote in message
...
If you really need the CodeName, not the Sheet name, try something like

Dim N As Integer
For N = 1 To 3
Debug.Print ThisWorkbook.VBProject.VBComponents("Sheet" & N).Name
Next N




"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to loop through 12 sheets, codename sheet1 thru sheet12

why doesn't this work?

i = 1
For i = 1 To 12
cname = "Sheet" & i
cname.Select
Next

--


Gary