willow1985
Well-known Member
- Joined
- Jul 24, 2019
- Messages
- 915
- Office Version
- 365
- Platform
- Windows
Hello,
I have the below code but am getting run time error 1004: "the cell or chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet. Youu might be requested to enter a password"
However as you can see in the code I already unprotected the sheet prior.
Why am I getting this runtime error if the sheet is unprotected?
Any help would be appreciated.
Thank you
Carla
I have the below code but am getting run time error 1004: "the cell or chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet. Youu might be requested to enter a password"
However as you can see in the code I already unprotected the sheet prior.
Why am I getting this runtime error if the sheet is unprotected?
Any help would be appreciated.
Thank you
VBA Code:
Sub NewDataBL()
'
' NewDataBL Macro
With ThisWorkbook.Sheets("New BL Data")
If Application.CountIf(.Range("B2:I2"), "") > 0 Then
MsgBox "Please Complete all Fields"
Exit Sub
End If
End With
Dim Msg As String, Ans As Variant
Msg = "Would you like to update the Batch Log with this Data?"
Ans = MsgBox(Msg, vbYesNo)
Select Case Ans
Case vbYes
Sheets("Batch Log").Select
Dim lo As ListObject
For Each lo In ActiveSheet.ListObjects
lo.AutoFilter.ShowAllData
Next lo
Worksheets("Batch Log").Unprotect Password:="SADIE"
Sheets("New BL Data").Select
Range("A2:J2").Select
Selection.Copy
Sheets("Batch Log").Select
Range("BLTable").Cells(1, 1).End(xlDown).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'GETTING THE ERROR ON THIS LINE'
Worksheets("Batch Log").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, Password:="SADIE"
Range("BLTable").Cells(1, 1).End(xlDown).Offset(1).Select
Sheets("New BL Data").Select
Range("B2:D2").Select
Selection.ClearContents
Range("F2:I2").Select
Selection.ClearContents
Range("B2").Select
ActiveWorkbook.Save
MsgBox "New Batch Submitted"
Case vbNo
GoTo Quit:
End Select
Quit:
'
End Sub
Carla