Macro to duplicate a protected sheet while keeping protection in place

taj2012

New Member
Joined
Jul 29, 2014
Messages
7
I have a Macro in place that duplicates data from one worksheet and creates a duplicate worksheet. I also have another Macro in place within the current worksheet that allows the group/ungroup funtion to be able to be used in the protected sheet. When I use the duplication Macro, the protection doesn't carry over. I need a Macro that holds the protection and duplicates it as is from worksheet 1 to worksheet 2
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Here is the code to create a duplicate "Outyear" or worksheet:

Sub Outyears()
Dim CurrentYear As Integer, NewName As String
If IsNumeric(Right(ActiveSheet.Name, 2)) Then
CurrentYear = Right(ActiveSheet.Name, 2)
ElseIf IsNumeric(Right(ActiveSheet.Name, 1)) Then
CurrentYear = Right(ActiveSheet.Name, 1)
Else
Exit Sub
End If
If CurrentYear >= 30 Then
MsgBox "You cannot go higher than 30"
Exit Sub
End If
CurrentYear = CurrentYear + 1
NewName = "20" & CurrentYear
Dim checkWs As Worksheet
On Error Resume Next
Set checkWs = Worksheets(NewName)
If checkWs Is Nothing Then
Worksheets(ActiveSheet.Name).Copy After:=Worksheets(ActiveSheet.Index)
ActiveSheet.Name = NewName
Else
Set checkWs = Nothing
MsgBox "A Worksheet named " & NewName & " already exists."
End If
End Sub


Here is the code I'm using which allows the group/ungroup function to be active within the protected sheet:

Private Sub Workbook_Open()
With Worksheets("2015")
.protect Password:="2015", Userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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