Search code examples
vbaexcelexcel-2013

Still Getting error "Can not do this to a protected sheet" even though macro is working properly?


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

Solution

  • The below re-write cleans up a few things. Hopefully with those taken care of, the sub should run without error. Namely:

    • Checks for existing sheet with same name to avoid potential clash
    • Uses .Value to avoid putting large amount of data in the clipboard
    • Avoids Selecting and Activating when not necessary
    • Fully qualifies ranges using ThisWorkbook

    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