Update Pivot Table and Enable Outlining on Open returns Runtime Error

[cesardltr]

Good Day,

I am very new to vba and am trying to make this work. I have a workbook with more than 20 sheets. Each one of them has a pivot table with a source in an external workbook and each one of them has an outline that allows group/ungroup a set of rows. I need all the pivot tables to be updated on opening the workbook and the outline to be enabled while keeping the sheets protected by the same password.

What I have know is this macro that is called in the Workbook Open event. When I open the workbook I get this 1004 Runtime Error in the method RefreshTable in the class PivotTable. In the debugging the line with Pivot.RefreshTable is highlighted. The part of enabling the outline works fine but the pivots are not updated. ¿Any thoughts on this? This is my code

Sub UpdateBS()
Dim Sheet As Worksheet, Pivot As PivotTable
    For Each Sheet In ThisWorkbook.Worksheets
        Sheet.Unprotect Password:="kvar"
        For Each Pivot In Sheet.PivotTables
            Pivot.RefreshTable
            Pivot.Update
        Next
        Sheet.EnableOutlining = True
        Sheet.Protect Password:="kvar", UserInterfaceOnly:=True
    Next
End Sub

Thank you folks

 

[cesardltr]

It definitely has to do with the protection. If I turn the protection off in all the worksheets, it works. ¿Anyone with an idea to overcome this?

Thanks