Rename the codename of the sheet and use that name in the remaining code

[praveenpatel421983]

Hi all,

This forum has always helped me when I get stuck with excel vba. I need help again.

I am trying to copy a worksheet named "Work" as "Job" in same workbook and trying to protect "Job" sheet. I came up to following code and I am stuck. Please help.

Sub RenameSht() 
Dim ws As Worksheet

ThisWorkbook.Sheets("Work").Copy After:=ThisWorkbook.Sheets(Sheets.Count)
ActiveSheet.Name = "Job"

For Each ws In ThisWorkbook.Worksheets
   If ws.Name = "Job" Then
      x = ws.CodeName
      y = "Rename1"
      ThisWorkbook.VBProject.VBComponents(x).Name = y
   End If
Next ws
    
If y.ProtectContents = False Then
   y.Protect "test"
    End If

End Sub

Thanks
Praveen

 

[Domenic]

Try...

Sub RenameSht()

    Dim ws As Worksheet
    
    ThisWorkbook.Sheets("Work").Copy After:=ThisWorkbook.Sheets(Sheets.Count)
    
    With ActiveSheet
        .Name = "Job"
        .Protect "test"
        ThisWorkbook.VBProject.VBComponents(Sheets(.Name).CodeName).Name = "Rename1"
    End With


End Sub

Hope this helps!

 

[praveenpatel421983]

Thanks Domenic for the reply. This is what I was missing. I will check this code, if I face any issue I will be back with more questions