How do I get the Source sheet to become password protected after using an active x button to make a copy of the active sheet?

[DMatt]

The code below is activated from an active x button. The code will unprotect the active workbook and worksheet, make a copy of the active worksheet, place that new sheet's tab where I want it to be placed, protect the new worksheet, and protect the workbook. It fails to protect the Source Worksheet that the copy was made from. I need the code to password protect the Last Active Sheet too (the Source Sheet the Copy was made from). Any help is greatly appreciated.

Private Sub CommandButton13_Click()
ThisWorkbook.Unprotect Password:="Password1"
ActiveSheet.Unprotect Password:="Password2"
Dim i As Long, s As String, s2 As String
s = "NEW WONDERFUL WORKSHEET"
s2 = s
ActiveSheet.Copy After:=Sheets(2)
Do While WorksheetExists(s2)
i = i + 1
s2 = s & " " & i
Loop
ActiveSheet.Name = s2
ActiveSheet.Protect Password:="Password2"
ThisWorkbook.Protect Password:="Password1"
End Sub

 

[rlv01]

Maybe something like this.

Private Sub CommandButton13_Click()
    Dim WS1 As Worksheet, WS2 As Worksheet
    Dim i As Long, s As String, s2 As String

    Set WS1 = ActiveSheet

    ThisWorkbook.Unprotect Password:="Password1"
    WS1.Unprotect Password:="Password2"

    s = "NEW WONDERFUL WORKSHEET"
    s2 = s

    WS1.Copy After:=Sheets(2)
    Set WS2 = ActiveSheet

    Do While WorksheetExists(s2)
        i = i + 1
        s2 = s & " " & i
    Loop

    WS2.Name = s2
    WS2.Protect Password:="Password2"
    WS1.Protect Password:="Password2"
    ThisWorkbook.Protect Password:="Password1"
End Sub

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

it makes the code easier to read.)

 

[DMatt]

Thank you rlv01, that did what I needed it to do.

 

[rlv01]

Glad it worked for you.