VBA Run time error 1004 stating a protected sheet when not protected??

[willow1985]

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

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

 

[willow1985]

I have no other code running. Not sure why it is giving me that message prompt causing the error.

 

[willow1985]

I see no problem in your code.
Do you have any other macro running on the sheet or in the book's events?

I pass your code a little compacted.

Sub NewDataBL()
  ' NewDataBL Macro
  Dim Msg As String, Ans As Variant, lr As Long, sh As Worksheet, lo As ListObject

  Application.ScreenUpdating = False
  If Application.CountIf(Sheets("New BL Data").Range("B2:I2"), "") > 0 Then
     MsgBox "Please Complete all Fields"
    Exit Sub
  End If
  Ans = MsgBox("Would you like to update the Batch Log with this Data?", vbYesNo)

  If Ans = vbYes Then
    Set sh = Sheets("Batch Log")
    For Each lo In sh.ListObjects
      lo.AutoFilter.ShowAllData
    Next lo
  
    sh.Unprotect Password:="SADIE"
    sh.ListObjects("BLTable").ListRows.Add AlwaysInsert:=True
    lr = sh.ListObjects("BLTable").ListRows.Count
    Sheets("New BL Data").Range("A2:J2").Copy
    sh.ListObjects("BLTable").DataBodyRange(lr, 1).PasteSpecial xlPasteValues
    sh.Protect Password:="SADIE", DrawingObjects:=True, Contents:=True, _
      Scenarios:=True, AllowSorting:=True, AllowFiltering:=True

    Sheets("New BL Data").Range("B2:D2, F2:I2").ClearContents
    Range("B2").Select
    ActiveWorkbook.Save
    MsgBox "New Batch Submitted"
  End If
End Sub

This code however seems to have fixed the problem. I no longer get the error.

Thank you very much! ?

 

[DanteAmor]

Im glad to help you, thanks for the feedback.