title: Refreshing pivot tables on locked sheet with data from sheet locked by diff password
body: <p>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. </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>Ideal outcome:</p>
  
  <p>(1) Have one password (such as Minneapolis) for sheets 1-4 where the field team can enter data into</p>
  
  <p>(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).</p>
  
  <p>(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.</p>