codename help

[Gary Keramidas]

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]

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

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:%[email protected]...
>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
>
>
>

[Carlos]

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
news:%[email protected]...
>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]

thanks carlos

--


Gary


"Carlos" <[email protected]> wrote in message
news:[email protected]...
> 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
> news:%[email protected]...
>>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]

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

--


Gary


"Carlos" <[email protected]> wrote in message
news:[email protected]...
> 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
> news:%[email protected]...
>>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
>>
>>
>>
>
>

[Tom Ogilvy]

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
news:[email protected]...
> i think that only works for the sheet name, not the codename
>
> --
>
>
> Gary
>
>
> "Carlos" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > news:%[email protected]...
> >>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]

thanks for the tip.

--


Gary


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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
> news:[email protected]...
>> i think that only works for the sheet name, not the codename
>>
>> --
>>
>>
>> Gary
>>
>>
>> "Carlos" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> > news:%[email protected]...
>> >>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
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Bruno Campanini]

"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:%[email protected]...
>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

[Bob Phillips]

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
news:%[email protected]...
> 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]

thanks for your help on this, bob

--


Gary


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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
> news:%[email protected]...
>> 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
>>
>>
>>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> news:%[email protected]...
> > 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 Pearson]

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
news:%[email protected]...
>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]

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").ClearContents
ActiveSheet.Protect
Range("a1").Select
Next

--


Gary


"Chip Pearson" <[email protected]> wrote in message
news:%[email protected]...
> 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
> news:%[email protected]...
>>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
>>
>>
>>
>
>

[Tom Ogilvy]

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
news:[email protected]...
> 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").ClearContents
> ActiveSheet.Protect
> Range("a1").Select
> Next
>
> --
>
>
> Gary
>
>
> "Chip Pearson" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> > news:%[email protected]...
> >>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]

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

--


Gary


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> 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
> news:[email protected]...
>> 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").ClearContents
>> ActiveSheet.Protect
>> Range("a1").Select
>> Next
>>
>> --
>>
>>
>> Gary
>>
>>
>> "Chip Pearson" <[email protected]> wrote in message
>> news:%[email protected]...
>> > 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
>> > news:%[email protected]...
>> >>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
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>