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

 

[sparky2205]

Hi Fluff, me again,
can you explain your earlier code to me please?
I'm struggling with it, especially how you can check for the existence of the Sheet Name in one action.
I haven't used either the Evaluate or isref functions before.

Thanks.

 

[Fluff]

IsRef is an Xl function https://exceljet.net/excel-functions/excel-isref-function
Whilst the Evaluate, evaluates a string formula and returns the result

 

[sparky2205]

Hi Fluff,
I now understand what IsRef and Evaluate do.
What I don't understand is how IsRef checks all worksheets without a loop.

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

Does IsRef automatically check all worksheets in the workbook when used in vba code?
I have seen examples where it is used in a workbook cell but only to refer to one worksheet at a time.
Or is it Evaluate that forces this?
I can't see this explained anywhere.

 

[Fluff]

It doesn't check all worksheets, it just checks to see if it's a valid reference (ie does the sheet exist)