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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top