So I thought I was finished with this code and it was working but when I navigate away from Sheet 1 to any other sheet and then back to sheet 1 A msgbox pops up and informs me that I can't do that to a protected sheet.
I am unclear as to why this is happening as the code is doing exactly what it is supposed to... Any help would be appreciated.
Edit: I probably should have mentioned that the Sheet is protected with the password "1". I realize this is not the most appropriate password, it is more for ease of access while I work through this problem.
Sub freezesheet()
'set variable for the naming of the new sheet
Dim newname As String
'assignes our open variable to a designated value
Sheets("Sheet1").Activate
newname = Sheets("Sheet1").Range("C2").Value
'copies the sheet to a new sheet after the designated tab
Sheets("Sheet1").Copy after:=Sheets(3)
ActiveSheet.Name = newname
'unprotects the sheet so we can copy and paste as values
ActiveSheet.Unprotect "1"
'makes all of the formulas on the sheets into values and returns you to the original sheet
Cells.Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Re-protects sheet to ensure that we don't make changes to historical data.
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
Sheets("Sheet1").Activate
End Sub
The below re-write cleans up a few things. Hopefully with those taken care of, the sub should run without error. Namely:
.Value
to avoid putting large amount of data in the clipboardThisWorkbook
See comments for details
Sub freezesheet()
'set variable for the naming of the new sheet
Dim newname As String
'assigns newname variable to a designated value
newname = ThisWorkbook.Sheets("Sheet1").Range("C2").Value
' Check if sheet name already exists
Dim sh as worksheet
On Error Resume Next
Set sh = ThisWorkbook.Sheets(newname)
On Error GoTo 0
If Not sh Is Nothing Then
MsgBox "Error: sheet name already exists, aborted"
Exit Sub
End If
'copies the sheet to a new sheet after sheet 3
ThisWorkbook.Sheets("Sheet1").Copy after:=Sheets(3)
With ThisWorkbook.Sheets(4)
.Name = newname ' New sheet was after sheet 3, so now sheet 4
'unprotects the sheet so we can copy and paste as values
.Unprotect "1"
'makes all of the formulas on the sheets into values
.UsedRange.Value = .UsedRange.Value
'Re-protects sheet to ensure that we don't make changes to historical data.
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
End With
ThisWorkbook.Sheets("Sheet1").Activate
End Sub