Copy Tabs with VBA

[roboed]

Hi.

I am trying to write a simple code which does the following, with the click of a button on its respective tab.

1. Unprotect all sheets with a password
2. Copies the selected / active sheet to a new sheet, placed at the end of existing sheets
3. Displays a message box to name the new sheet
4. Protect all sheets with a password

Reason for nr 1 and 4 is error messages when copying and moving between the sheets while protected - this trick removed the error. And the sheets needs to be protected for "idiot-proofing" reasons.

But, the wrong sheet gets copied. Assume I have 5 sheets: A, B, C, D and E.
If my active sheet is "E", and I press my macro-connected button it copies "D".
If my active sheet is "A", and I press my macro-connected button it still copies "D".

I suspect steps 1 and/or 4 is messing with something...

Fairly new to coding, so cut and paste from various online sites + trying and failing is my method of working. But great fun!

Any tips or tricks?

Code:

Sub

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect "password123"
Next ws


ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = InputBox("Name of New Invoice Tab:")

For Each ws In ThisWorkbook.Worksheets
ws.Protect "password123"
Next ws

End Sub

 

[roboed]

Hi.

I am trying to write a simple code which does the following, with the click of a button on its respective tab.

1. Unprotect all sheets with a password
2. Copies the selected / active sheet to a new sheet, placed at the end of existing sheets
3. Displays a message box to name the new sheet
4. Protect all sheets with a password

Reason for nr 1 and 4 is error messages when copying and moving between the sheets while protected - this trick removed the error. And the sheets needs to be protected for "idiot-proofing" reasons.

But, the wrong sheet gets copied. Assume I have 5 sheets: A, B, C, D and E.
If my active sheet is "E", and I press my macro-connected button it copies "D".
If my active sheet is "A", and I press my macro-connected button it still copies "D".

I suspect steps 1 and/or 4 is messing with something...

Fairly new to coding, so cut and paste from various online sites + trying and failing is my method of working. But great fun!

Any tips or tricks?

Code:

This seemed to fix it

Sub

Dim ws As Worksheet

ActiveSheet.Unprotect "password123"

ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = InputBox("Name of New Invoice Tab:")

For Each ws In ThisWorkbook.Worksheets
ws.Protect "password123"
Next ws

End Sub