Refresh Pivot Table Connected to Protected Sheet

Hi All,

I have a worksheet that has pivot tables on a few different tabs using the same data source.

I ran into the error of "cannot refresh" because some of the sheets are password protected.

I am using the following code right now to be able to refresh the pivot tables while locking the worksheets again.

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

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

ActiveWorkbook.RefreshAll

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

End Sub


The problem with this is that in the protected worksheets I don't want to lock the cells, I just want to hide them. Also I want to allow users in the locked sheets to be able to do everything, i.e. under protect sheet they have select columns, delete rows, insert hyperlinks etc. I would like all of these to be selected.

Basically I want to allow user the full functionality of the worksheet but hide the formulas.

Also just noticed that using this code will protect all my sheets. I just want to protect a few sheets instead of all of them.

Any help would be fully appreciated thank you.