Using VBA macro, I want to copy, rename and protect excel sheet. I have allocated this macro to a button. So every time I click this button a new sheet is created with new name which is next to the previous sheet name and Workbook must be protected in such a manner that sheets created cannot be deleted. I’m getting error when I run my code
Below is my code:
Sub NewPage()
Dim randomNumber As Integer
Dim answer As Integer
' randomNumber = Int(2 + Rnd * (100000 - 2 + 1))
' randomNumber = Rnd() * 10000
With Sheets("Step 1")
If ThisWorkbook.Worksheets.Count < 12 Then
.Copy after:=Sheets("Step 2")
Else
answer = MsgBox("You can only have 10 sheets", vbOKOnly)
Exit Sub
End If
End With
With ActiveSheet
randomNumber = Rnd() * 10000
' .Unprotect Password:="ERiQByLY*dD?4cNfY4u97"
' ActiveWorkbook.Unprotect "ERiQByLY*dD?4cNfY4u97"
Range("C1:D1").ClearContents
Range("C14").ClearContents
Range("F1").ClearContents
Range("H14:G19").ClearContents
Range("A4:A14").ClearContents
Range("G4:G13").ClearContents
With Range("H16").Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = "16"
End With
With Range("H17").Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = "13"
End With
.Name = "LB" & "" & randomNumber
With .UsedRange
.Value = .Value
End With
Range("C3:F13").Locked = True
Range("C15:D19").Locked = True
' .Protect Password:="ERiQByLY*dD?4cNfY4u97"
' ActiveWorkbook.Protect "ERiQByLY*dD?4cNfY4u97"
End With
End Sub
Below is my code:
Sub NewPage()
Dim randomNumber As Integer
Dim answer As Integer
' randomNumber = Int(2 + Rnd * (100000 - 2 + 1))
' randomNumber = Rnd() * 10000
With Sheets("Step 1")
If ThisWorkbook.Worksheets.Count < 12 Then
.Copy after:=Sheets("Step 2")
Else
answer = MsgBox("You can only have 10 sheets", vbOKOnly)
Exit Sub
End If
End With
With ActiveSheet
randomNumber = Rnd() * 10000
' .Unprotect Password:="ERiQByLY*dD?4cNfY4u97"
' ActiveWorkbook.Unprotect "ERiQByLY*dD?4cNfY4u97"
Range("C1:D1").ClearContents
Range("C14").ClearContents
Range("F1").ClearContents
Range("H14:G19").ClearContents
Range("A4:A14").ClearContents
Range("G4:G13").ClearContents
With Range("H16").Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = "16"
End With
With Range("H17").Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = "13"
End With
.Name = "LB" & "" & randomNumber
With .UsedRange
.Value = .Value
End With
Range("C3:F13").Locked = True
Range("C15:D19").Locked = True
' .Protect Password:="ERiQByLY*dD?4cNfY4u97"
' ActiveWorkbook.Protect "ERiQByLY*dD?4cNfY4u97"
End With
End Sub