Copy Tabs with VBA

roboed

New Member
Joined
Nov 30, 2018
Messages
2
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

New Member
Joined
Nov 30, 2018
Messages
2
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
 

Forum statistics

Threads
1,086,253
Messages
5,388,698
Members
402,136
Latest member
Miki

Some videos you may like

This Week's Hot Topics

Top