Refreshing pivot tables on locked sheet with data from sheet locked by diff password

Question

I'm having a number of issues designing a VBA that will allow me to refresh pivot tables in one sheet with data from another sheet.

There are five sheets that we want our field teams to access, enter data into and edit (as a procurement tracker). These five sheets are to be protected by one password (say, Minneapolis). Data from these five sheets feed into pivot tables on the next six sheets and we would like these protected by a different password that only HQ knows (say, jambalaya). All of these sheets (i.e. those with pivot tables) tab names start with LOG. Following these eleven sheets are two sheets for guidelines. We're also working in a binary format but have also tried the macro-enabled format (although not necessarily with all the different iterations of code). We preferred binary because it makes for a smaller file size and this is important for, for ex., teams in South Sudan with slow and limited internet access but need to be able to send this back and forth to HQ.

Our problem is that in order to get the pivot tables to refresh, we have to unprotect, refresh and reprotect the sheets and we're meeting problems here. I've found a number of examples of solutions for this on the internet but we're having trouble with things like autofilters not working properly, some sheets being left unlocked, or fixing these but not being able to refresh the pivot tables.

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

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

ActiveWorkbook.RefreshAll

For Each ws In ActiveWorkbook.Worksheets
  ws.Protect Password:="jambalaya", _
     AllowUsingPivotTables:=True
    ActiveSheet.Protection.AllowFiltering = False
 ActiveSheet.Protect AllowFiltering:=True

Next ws
    Application.Calculate
End Sub

If I run this code in the VBA on the page with data that feeds into the pivot tables (called PROCUREMENT TRACKER), the autofilters work and other pages are properly locked, and this page is locked but without a password. If I run it from a button linked to that macro on the PROCUREMENT TRACKER page, the autofilters on the page don't work (and for some reason it lands on the second to last page, one of the guidance pages). There is also still the issue of wanting to have a different password for this, and if we start it with Minneapolis, it'll revert to jambalaya anyway. I was hoping that it would be possible to have it only refresh pages with the password jambalaya (and have tried a couple different codes for that), but it still changes the page it's run on even if that password is Minneapolis. I've also seen some options where you prompt the user to enter in the password but we DONT want to give the teams access to the password jambalaya, ONLY Minneapolis.

Sub LetsTryThis()
Dim I As Integer
On Error Resume Next


For Each Worksheet In ActiveWorkbook.Worksheets
    If Current.Name Like "LOG*" Then
        Worksheet.Unprotect Password:="jambalaya"
    End If
Next

ActiveWorkbook.RefreshAll

For Each Worksheet In ActiveWorkbook.Worksheets
    If Worksheet.Name Like "LOG*" Then
        Worksheet.Protect Password:="jambalaya", _
            AllowUsingPivotTables:=True
            ActiveSheet.Protection.AllowFiltering = False
        ActiveSheet.Protect AllowFiltering:=True
    End If
Next

End Sub

Because the pages with pivot tables all start with LOG, I tried doing this code above as well, hoping it would only refresh the sheets with pivot tables. If I run this macro in the VBA from the PROCUREMENT TRACKER sheet, there is an issue of leaving the PROCUREMENT TRACKER sheet locked but without a password, and then if I do it from the button on the PROCUREMENT TRACKER sheet, it lands on the last LOG sheet (i.e. the ones with pivot tables), leaving that page protected without a password and it leaves the PROCUREMENT TRACKER sheet (again, the one with the button and the data used in the pivot tables) completely unprotected.

Ideal outcome:

(1) Have one password (such as Minneapolis) for sheets 1-4 where the field team can enter data into

(2) Have a second password (such as jambalaya) for sheets 5-12 with the pivot tables (and guides) that only HQ knows. These pivot tables pull data from sheet 3 (PROCUREMENT TRACKER).

(3) Find a way to refresh the pivot tables on tabs 5-10 without leaving anything unprotected/protected without password in the process and without altering features such as the use of autofilters.

Scott Holtzman · Accepted Answer · 2018-01-19T21:00:41.877

0

The biggest issue you are facing here is the use of ActiveSheet. Stay away from that unless absolutely needed. It's causing behavior that you feel like you can't trace - as it often does.

Do the following

Manually protect Minneapolis sheets as needed. Then never do anything with them :)
Use this code for refresh.

Code

Option Explicit

Sub RefreshData()

    ProtectPivotSheets False

    ThisWorkbook.RefreshAll

    ProtectPivotSheets True

End Sub

Sub ProtectPivotSheets(switch As Boolean)

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        If ws.Name Like "LOG*" Then

            If switch Then
                ws.Protect Password:="jambalaya", AllowUsingPivotTables:=True
            Else
                ws.Unprotect "jambalaya"
            End If

        End If

    Next

End Sub

edited Jan 19 '18 at 21:00

answered Jan 19 '18 at 18:51

Scott Holtzman

27,099
5
37
72

Thank you for this Scott--very helpful and appreciated! In case someone else wants to use this code in the future, there is a small typo (swtich instead of switch) but as soon as that's changed it works. – S. Brown Jan 19 '18 at 20:21
@S.Brown - glad it works. Fixed the typo. You can mark as answered by clicking the grey check mark on the top left next to the answer text. – Scott Holtzman Jan 19 '18 at 21:01

Refreshing pivot tables on locked sheet with data from sheet locked by diff password

1 Answers1