Workbook protection macro only works on step thru

Hi,
I have a workbook that is connected with a SharePoint list. When the workbook is refreshed, the list populates a table in the workbook. The workbook should remain protected, allowing only sort, autofilter, pivot tables, and cell formatting.

Right now I have code to remove formatting and autofilters when the workbook is opened, then protect it. A second macro is run via a button, and it is supposed to unprotect the work book, copy and fill some calculations, then protect it again.

Here are the two bits:

Private Sub Workbook_Open()

Sheets("Input").Activate
ActiveSheet.Unprotect
ActiveSheet.AutoFilterMode = False
Range("Table_owssvr_1").ClearFormats
ActiveSheet.ListObjects("Table_owssvr_1").TableStyle = "TableStyleMedium9"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

End Sub

Sub Refresh_Data_and_Pivots()
'
Sheets("Input").Activate
ActiveSheet.Unprotect
ActiveWorkbook.RefreshAll
Range("AY1:CH1").Copy
ActiveSheet.Paste Destination:=Range("AY4")
Range("AY4:CH4").AutoFill Destination:=Range("AY4:CH" & Cells(Rows.Count, "A").End(xlUp).Row), Type:=xlFillDefault
Sheets("Customer_Segment").PivotTables("PivotTable1").RefreshTable
Range("A3").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub

The problem is that on the second macro, the ActiveWorkbook.RefreshAll command throws the following message:

"The cell or chart that you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove the protection Using the Unprotect Sheet command."

The funny thing is, I can step through using F8, and it works just fine.

There may be experts around who can diagnose the problem from the code alone but, for me, the workbook is an essential input to the diagnostic process.

Regards

Good luck.