Pivot Table(s) update macro workbook with locked sheets

**torers** · 06-08-2015, 01:00 PM

Problem follows, hope it is Clear:
-------------------------------------

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

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

ActiveWorkbook.RefreshAll

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

End Sub

' 1. Getting error message on opening workbook, because:
' I have 2 (locked) sheets with different Pivot Tables based on same source data!
' NOTE All sheets are locked, not Workbook
'
' BUT:
' 2. When I run this macro from an "Update" Button,in either of the Pivot sheets
' -the macros are executed despite the error message
' -but the last referenced sheet is opened! (ARTICLES)
' (Sheets CUSTOMER1-CUSTOMER2-TOTALS(Pivot)-ANALYSIS(Pivot)-CUSTOMERS-ARTICLES)
' "ARTICLES" is opening....

' 1. Don't understand, as the macro is executing anyway. ?
' 2. What I am missing is a substitute, or an addition to, "Next ws". ?
' 3. NOTE that macro is excecuting correct whwn RUN, but lands on last sheet from BUTTON.

**torers** · 06-13-2015, 12:02 PM

I have (partially) answered my own Qs:

1. Reason for error message: (Cannot refresh pivot table while sheet is protected)

"Pointing to a different sheet, means that each one of them is using a different pivot cache, which although different is of the same size on each sheet, which was exactly what was causing the problem
Changing the pivot source of each pivot table to include just one row more than the previous one solves it. (Citation from contributor "wakeupcall"). Now to relearn Pivot details....

2. Macro updating button in one (of 2) Pivot Table sheets opens irrelevant last sheet-
REPLACED macro button with ACTIVEX ditto, both tables updated and remains on Pivot Table sheet 1.

RGDS

**torers** · 06-13-2015, 12:49 PM

I have a simple solution for Question 1, it seems:
-Pivot Table Alternatives
-Data
-Uncheck "Update on opening file"

This of course may depend on other circumstances. In my case I only have the 2 table sheets to update, taken care of by the Update ACTIVEX on sheet 1

NOTE:
-Workbook not locked, sheets only

Tested on Excel 2013 Home/Office 2010 PRO

Comments?