Error message with macro that removes/restores protection of source sheet and pivot tables

[jfrsanders]

I am receiving an error message each time I open a file that contains a macro that removes protection from the source/pivot tables, refreshes them, and then reinstates the protection (among other things). I assume that Excel has a problem with the macro for some reason, even though I had used it repeatedly without the error message. The error message says:

and the macro is as follows:

Sub UnprotectRefreshAll()
Dim ws As Worksheet
On Error Resume Next

For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="Matilda"
Next ws

ActiveWorkbook.RefreshAll

For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="Matilda", _
AllowUsingPivotTables:=True
Next ws

Worksheets("AcEmLi").Activate

SendKeys "^{PGDN}"
SendKeys "^%{F5}"
SendKeys "{ENTER}"
SendKeys "{ENTER}"
SendKeys "^{PGUP}"

Sheets("AcEmLi").Unprotect Password:="Matilda"

Range("Table17[[#Headers],[Last Name]]").Select
ActiveWorkbook.Worksheets("AcEmLi").ListObjects("Table17").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("AcEmLi").ListObjects("Table17").Sort.SortFields. _
Add2 Key:=Range("Table17[[#All],[Last Name]]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("AcEmLi").ListObjects("Table17").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With





Sheets("AcEmLi").Protect Password:="Matilda"





End Sub



_________________________

The first part of the macro refreshes the source table and pivot tables, the second part of the macros presses enter twice to get rid of an error of unknown origin, and the last part just sorts a column of the source table. As I said, this macro seemed to work for a while, but then starting causing the above error message to appear upon opening my file.

If anybody could give me some words of wisdom as to why this may be happening, I would greatly appreciate it.

Thanks!

 

[Sam_D_Ben]

Carefully reread the error message multiple times, and you'll find the solution.

"The message indicates that a sheet is still locked. Try unlocking it manually, AND your script is fine, but it would be better if you update it."

 

[jfrsanders]

Carefully reread the error message multiple times, and you'll find the solution.

"The message indicates that a sheet is still locked. Try unlocking it manually, AND your script is fine, but it would be better if you update it."

Thanks Sam_D_Ben. I'll try to figure out why a particular sheet is still locked, and then try to adjust the macro to prevent this.