Refresh Pivot on Protected Sheet - error even though unprotect in VBA

Question

I have a pivot table that automatically refreshes on sheet activation, with data from another tab which is protected. Even though I unprotect the tab prior to refreshing, I still get:

Run-time error '1004':
Cannot edit PivotTable on protected sheet.

My VBA:

Private Sub Worksheet_Activate()
   Sheets("Extract").Unprotect "password"
   ActiveSheet.PivotTables("pvt_Activity").PivotCache.Refresh
   Sheets("Extract").Protect Password:="password", Contents:=True, Scenarios:=True
End Sub

Banging my head against a wall - why is this not working?

Cheers

Taken literally, the error message would suggest it's the other (active) sheet that's protected. Have you tried unprotecting that too? — Reg Edit
I can't repro based on what you've provided (assuming the active sheet is unprotected). — BigBen
The active sheet is not protected. What's weird is if I change the data source (by adding 1 row or removing 1 row) all of a sudden it works. It's only when I save, and reopen the file I get the error again. — Jamie Sutton
Still no repro. I don't even need to unprotect/protect. Can you explain more about your setup? — BigBen
2 sheets - 1 sheet where staff put data dumps from our bespoke system into. There are defined headers and a macro to run to ensure the data is valid. Once validated, they move to the second sheet which is the pivot table off of that data (it's a named range with offset as the data could grow/shrink depending on the week). If i alter the offset to minus one row the pivot refreshes. Once I save and reopen the file I get this error. — Jamie Sutton

Neelavathy Neelavathy · Accepted Answer · 2019-10-30T11:38:01

Since pivot tables are in protected sheets, you get this error,so you need to unprotect them before refreshing

so have a refresh button in each sheet and assign this macro below,

g_sPassword-> is the password to unprotect the sheet, I have declared that as a variable ,so I use it directly

Sub RefreshPtInFirstsheet()

Call RefreshPivotTables(sheetname1)

End Sub

Sub RefreshPtInSecondsheet()

Call RefreshPivotTables(sheetname2)

End Sub

Sub RefreshPivotTables(sht As Worksheet)

Dim pt As pivotTable

sheetname1.Unprotect g_sPassword
sheetname2.Unprotect g_sPassword
For Each pt In sht.PivotTables
    pt.PivotCache.Refresh
    Exit For
Next pt
sheetname1.Protect g_sPassword
sheetname2.Protect g_sPassword
MsgBox "Pivot Table Updated"

End Sub

Refresh Pivot on Protected Sheet - error even though unprotect in VBA

1 Answers