I have a master workbook with which I can produce slaves from but I am looking at keeping certain cells protected to stop deletion (dates etc). What I require is for the top 5 rows to be protected on each of the 4 week sheets where the dates etc are. I had played with protecting all the sheets and with unprotect then protect on the master but it did not carry over to the newly created slave, could someone point me in the right direction please.
SOLVED: The code is not pretty but functional.
Private Sub CommandButton1_Click()
Dim nPeriod As Long
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Unprotect Password:="password"
Next wSheet
nPeriod = Application.InputBox("Which period", Type:=1)
If nPeriod = Cancel Or nPeriod < 1 Or nPeriod > 13 Then Exit Sub
If Worksheets("Week 1").Visible = False Then
Worksheets("Week 1").Visible = True
End If
If Worksheets("Week 2").Visible = False Then
Worksheets("Week 2").Visible = True
End If
If Worksheets("Week 3").Visible = False Then
Worksheets("Week 3").Visible = True
End If
If Worksheets("Week 4").Visible = False Then
Worksheets("Week 4").Visible = True
End If
Sheets(Array("Week 1", "Week 2", "Week 3", "Week 4", "Dates")).Copy
With ActiveWorkbook
.Sheets("Week 1").Range("E2").Value = nPeriod
For Each wSheet In Worksheets
wSheet.Protect Password:="password"
Next wSheet
.SaveAs "C:\Documents and Settings\sys001.d003\Desktop\MasterDummy\DUMMY JOURNEY Period " & nPeriod
'.SaveAs "C:\DUMMY JOURNEY Period " & nPeriod
.Close
End With
If Worksheets("Week 1").Visible = True Then
Worksheets("Week 1").Visible = False
End If
If Worksheets("Week 2").Visible = True Then
Worksheets("Week 2").Visible = False
End If
If Worksheets("Week 3").Visible = True Then
Worksheets("Week 3").Visible = False
End If
If Worksheets("Week 4").Visible = True Then
Worksheets("Week 4").Visible = False
For Each wSheet In Worksheets
wSheet.Protect Password:="password"
Next wSheet
End If
End Sub
Bookmarks