On consideration your method has significient advantages. This seems to work:

Sub alpha()
' Declare the proceedure variables
Dim VBComp As VBComponent
Dim sOldCodeName As String, iShtCntr As Integer
Dim sNewCodeName As String, sNewShtName As String
Dim sht As Worksheet, shtNewSheet As Worksheet
' Add the worksheet
ThisWorkbook.Sheets.Add
sNewShtName = ActiveSheet.Name
' Identify the new sheet code name
For Each VBComp In ThisWorkbook.VBProject.VBComponents
If LCase(VBComp.Properties("Name").Value) = _
LCase(sNewShtName) Then
sOldCodeName = VBComp.Properties("_CodeName").Value
Exit For
End If
Next VBComp
' Count sht that have been renamed
iShtCntr = 0
For Each sht In ThisWorkbook.Sheets
If Left(sht.CodeName, 6) = "MyCdNm" Then
iShtCntr = iShtCntr + 1
End If
Next sht
' Develop the new codename
If iShtCntr = 0 Then
sNewCodeName = "MyCdNm" & "Sheet1"
Else
sNewCodeName = "MyCdNm" & "Sheet" & iShtCntr + 1
End If
' Name the new worksheet
ThisWorkbook.VBProject.VBComponents(sOldCodeName). _
Name = sNewCodeName
End Sub


"Tom Ogilvy" wrote:

I don't think that there is much difference between the ways that Tom
Ogilvy
and I have identified the new sheet's codename,

Using the name of the new sheet specifically with the vbcomponent properties
vice counting sheets I would think is quite a bit different. I assume you
will rename all sheets to your code name - otherwise your method could fail
(and this assumes the added sheet will aways be the last - I can't say that
is a good assumption or not).

Plus, in the original question, you said that asking for the codename when
the vbe had not been opened caused problems - however, aren't you doing that
he

sOldCodeName = VBComp.Name
Granted, your original was
Sheets(sNewShtName).CodeName

so perhaps using the VBComp.Name isn't a problem, but wondered if you had
tested that situation. (as i recall, that still caused problems, thus
reverting to the properties approach - but it has been a while since I have
played with it).

Not trying to give you a hard time, but I would see this solution as very
specific to your situation.

--
Regards,
Tom Ogilvy



"Alasdair Stirling" wrote in
message ...
Sorry about creating a new thread, it's the first time that I have posted
to
the discussion.

I don't think that there is much difference between the ways that Tom
Ogilvy
and I have identified the new sheet's codename, but as correctly pointed
out
I have been able to reduce the number of loops as follows:

Sub alpha()
' Declare the variables
Dim VBComp As VBComponent, iExistCntr As Integer
Dim iNewCntr As Integer, sOldCodeName As String
Dim sNewCodeName As String
Dim sht As Worksheet, shtNewSheet As Worksheet
Dim iShtCntr As Integer
' Count the existing sht with name starting "Sheet"
iExistCntr = 0
For Each VBComp In ThisWorkbook.VBProject.VBComponents
If VBComp.Type = vbext_ct_Document Then
If Left(VBComp.Name, 5) = "Sheet" Then
iExistCntr = iExistCntr + 1
End If
End If
Next VBComp
' Add the worksheet
ThisWorkbook.Sheets.Add
' Re-count the existing sht with name starting "Sheet"
iNewCntr = 0
iShtCntr = 0
For Each VBComp In ThisWorkbook.VBProject.VBComponents
If VBComp.Type = vbext_ct_Document Then
If Left(VBComp.Name, 5) = "Sheet" Then
iNewCntr = iNewCntr + 1
If iNewCntr = iExistCntr + 1 Then
sOldCodeName = VBComp.Name
End If
End If
End If
If Left(VBComp.Name, 6) = "MyCdNm" Then
iShtCntr = iShtCntr + 1
End If
Next VBComp
' Develop the new codename
If iShtCntr = 0 Then
sNewCodeName = "MyCdNm" & "Sheet1"
Else
sNewCodeName = "MyCdNm" & "Sheet" & iShtCntr + 1
End If
' Name the new worksheet
ThisWorkbook.VBProject.VBComponents(sOldCodeName). _
Name = sNewCodeName
End Sub