Get Worksheet Name from CodeName

[sparky2205]

Hi folks,
I have the following macro which allows the copying of worksheets on a protected workbook.
It works fine but I was trying to replace:

sInterimName = ActiveSheet.Name

with something like

sc = Worksheets.Count
sInterimName = "Sheet" & sc.Name

In essence I want to retrieve the worksheet name using the codename.
Is this something straightforward?

Sub CopySheet()
Dim sName As String
Dim sInterimName As String
Dim iCountName As Integer
Dim ws As Worksheet
Dim pw As String
Dim sc As Integer
    pw = ""
    ScreenUpdating = False
    iCountName = 0
    sName = InputBox("Enter the worksheet name to copy")
    
    If sName = "" Then
        Exit Sub
    End If
    
    For Each ws In ThisWorkbook.Worksheets
        If UCase(ws.Name) = UCase(sName) Then
            iCountName = iCountName + 1
        End If
    Next ws
    
    If iCountName = 0 Then
        MsgBox "The selected worksheet name does not exist in this file"
        Exit Sub
    End If
    
    ThisWorkbook.Unprotect Password:=pw
    Sheets(sName).Copy after:=Sheets(sName)
    sInterimName = ActiveSheet.Name
    
    ThisWorkbook.Protect Password:=pw, structure:=True
                   
    Worksheets(sInterimName).Protect Password:=pw, _
        userinterfaceonly:=True, _
        AllowFiltering:=True, _
        AllowFormattingCells:=True, _
        AllowFormattingRows:=True, _
        AllowFormattingColumns:=True, _
        AllowInsertingRows:=True
        
        ScreenUpdating = True
        
End Sub

 

[jkpieterse]

I'm not sure why you want to use the codename?

 

[Fluff]

I'm not sure why you need the codename in this situation either, but you can replace this portion of your code

    For Each ws In ThisWorkbook.Worksheets
        If UCase(ws.Name) = UCase(sName) Then
            iCountName = iCountName + 1
        End If
    Next ws
    
    If iCountName = 0 Then
        MsgBox "The selected worksheet name does not exist in this file"
        Exit Sub
    End If

with

    If Not Evaluate("isref('" & sname & "'1A1)") Then
        MsgBox "The selected worksheet name does not exist in this file"
        Exit Sub
    End If

 

[sparky2205]

Knowledge, gentlemen.
I was initially taking the target sheet name and adding " (2)" to it.
That was fine as long as the name of the newly copied sheet didn't end up exceeding 31 characters with the addition of " (2)".
This, of course, would get abbreviated and my sInterimName would then be incorrect.
So I changed to ActiveSheet.Name.
As I said the code works. I am just trying to specify the worksheet by name in case I should need that in the future.
I thought the best way to do that would be to use the codename as the copied sheet will always be the last sheet added so will have the max codename.
If there is another way to get the sheet name by all means please enlighten me. I am here to learn.

Thanks for the new piece of code to check if the worksheet name exists Fluff.
I imagined there had to be a neater way of doing this but what I had worked so I stuck with it. (Ironic I suppose, considering this post).
I'll have a look and see if I can figure out exactly what is happening in it. Some new stuff there I haven't seen before.

 

[jkpieterse]

You already have the name! After your code has hit this line in your macro:

sInterimName = ActiveSheet.Name

 

[sparky2205]

Yes, I understand that, but what I was trying to do was replace that line of code with another that would get me the worksheet name by specifying the sheet rather than just using ActiveSheet.
That's why I was using the codename. The codename doesn't change.
And as the copied sheet will be the last sheet added to the workbook the numerical part of the codename e.g. Sheet22 will always be the max in the worksheet.
So, I was using Worksheet.Count to get that numerical value and append it to "Sheet" to get the codename.
Then the idea was to use that codename to retrieve the name of the worksheet.
That's when I ran into problems and contacted you guys.

 

[Fluff]

You cannot get the codename by concatenating values together like. Also the codename acts the same as a normal sheet name if sheets are deleted or renamed.
So if you deleted codename Sheet5 and at a later date added a new sheet it would be Sheet5 not Sheet23.

 

[sparky2205]

So Excel fills in the blanks, so to speak, when creating new worksheets. I didn't realise that.
So, ActiveSheet it is then.
Thanks for your help folks.

 

[Fluff]

You're welcome & thanks for the feedback

 

[jkpieterse]

When you copy a worksheet it always becomes the activesheet, so if you catch the name (or set an object reference) of the activesheet just after the copy, you're good to go.