Pivot Table Error Upon File Opening

[lrp20]

Hi Everyone,

I'm new to VBA and have created a database file which, thanks to lots of helpful posts on this forum, as well as Google and YouTube actually does what we want it to do!

I'm coming unstuck by a small, yet annoying, problem however. Upon opening the file users are met with this message: "That command cannot be performed while a protected sheet contains another PivotTable report based on the same source data. To remove protection from the sheet that has the other report, click the sheet tab, and then click Unprotect Sheet, then try again."

The file does include a few pivot tables, all on separate tabs, that are all based off the main data table (which is located on a a tab called 'Master Database'). So I understand that Excel thinks the user is trying to do something to one or other of these pivot tables - the issue is that I'm not sure why when the only thing the user has done is to open the file.

I have included the Workbook_Open code below. Please could I ask for help identifying where this is attempting to do something that would create this error? Huge thanks!!

Private Sub Workbook_Open()

'Application.AskToUpdateLinks = False
'Application.WindowState = xlMaximized

'On Error Resume Next

Dim ws As Worksheet


Dim Start_Here As Worksheet
Dim Entry_Form As Worksheet
Dim Update_Form As Worksheet
Dim Approval As Worksheet


Set Start_Here = Worksheets("Start Here")
Set Entry_Form = Worksheets("New Efficiency")
Set Update_Form = Worksheets("Update Efficiency")
Set Approval = Worksheets("Approval Form")



   For Each ws In Worksheets

       ws.Visible = xlSheetHidden
       ws.Protect "LE_OC_2016", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
       AllowFiltering:=True, AllowUsingPivotTables:=True


       If ws.Name = "Start Here" Or ws.Name = "Form Guidance" Or ws.Name = "Front Sheet" Then
           ws.Visible = True
       End If

       If ws.Name = "New Efficiency" Or ws.Name = "Update Efficiency" Or ws.Name = "Approval Form" Then
           ws.Unprotect "LE_OC_2016"
       End If

   Next ws


'Prevents screen flickering/switching & other code running when button pressed
       With Application
           .ScreenUpdating = False
           .EnableEvents = False
           .CutCopyMode = True
       End With

           Entry_Form.Activate

           Range("$B$3,$K$3,$B$6,$B$9:$F$23,$B$26:$F$28,$B$30:$F$36,$K$7,$K$8,$N$8,$K$9:$O$19,$K$22:$O$28,$H$31,$C$40:$N$42,$C$47,$F$47,$F$48,$C$51,$F$51,$F$52,$J$47,$M$47,$M$48,$J$51,$M$51,$M$52,$A$55").Select

           Selection.ClearContents



           Update_Form.Activate

           Range("$B$3,$K$3,$B$6,$B$9:$F$23,$B$26:$F$28,$B$30:$F$36,$K$7,$K$8,$N$8,$K$9:$O$19,$K$22:$O$28,$H$31,$C$40:$N$42,$C$47,$F$47,$F$48,$C$51,$F$51,$F$52,$J$47,$M$47,$M$48,$J$51,$M$51,$M$52,$A$55,$N$2").Select


           Selection.ClearContents


           Approval.Activate

           Range("$C$1,$C$8,$C$10:$C$20,$D$25:$N$27,$C$28:$O$30,$C$32,$D$48:$N$48,$C$50,$D$55:$N$55,$C$57,$D$63:$N$63,$C$66").Select

           Selection.ClearContents



'Turns back on
       With Application
           .ScreenUpdating = True
           .EnableEvents = True
           .CutCopyMode = False
       End With



   For Each ws In Worksheets

       If ws.Name = "New Efficiency" Or ws.Name = "Update Efficiency" Or ws.Name = "Approval Form" Then
           ws.Protect "LE_OC_2016", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
           AllowFiltering:=True, AllowUsingPivotTables:=True
       End If

   Next ws




Start_Here.Activate




End Sub

 

[RedBeard]

Your code does protect the LE_OC_2016 sheet straight away, so I'm guessing you have a pivot table in there that tries to refresh and fails because it's protected.

 

[lrp20]

Hi RedBeard,

Many thanks for the reply. Forgive my slowness but I'm a little confused - LE_OC_2016 is the password that users must enter to unprotect each of the sheets that the code protects (or at least that I think it protects if I've got the code set up correctly).

I agree that one/some of the pivot tables in the file must be trying to refresh and that's what's throwing up the error - however I'm not sure why as the Workbook_Open() code doesn't include anything about pivot table refreshing (again, if I'm looking at the code correctly?).

Apologies if I'm being frustrating, I genuinely do appreciate any help you and others can cast my way.

 

[lrp20]

Hello again,

I can't believe I've been so silly - was busy putting a light up and it suddenly occured to me to double check my pivot table options. Sure enough under 'Data' one of the tables had the 'Refresh data when opening the file' box ticked. What a nonsensical mistake to make. Thanks RedBeard, and I'm sorry for wasting your time.

 

[RedBeard]

Good stuff!
Good you got it sorted, it's usually the small things that are the most annoying.

 

[henryjhu]

Hi,

I am having the same error. But even after I had the 'Refresh data when opening the file box' ticked, I still got the same error. Below are my code.

Woorkbook class:

Private Sub Workbook_Open()

Worksheets("EG_Pivot").Protect Password:="bbc2019", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Worksheets("L4_Pivot").Protect Password:="bbc2019", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

End Sub


Worksheet class:

Private Sub Worksheet_Change(ByVal Target As Range)

'On Error Resume Next

Worksheets("EG_Pivot").Unprotect Password:="bbc2019"
Worksheets("L4_Pivot").Unprotect Password:="bbc2019"

Worksheets("EG_Pivot").PivotTables(1).PivotCache.Refresh
Worksheets("L4_Pivot").PivotTables(1).PivotCache.Refresh

Worksheets("EG_Pivot").Protect Password:="bbc2019", AllowUsingPivotTables:=True, UserInterfaceOnly:=True
Worksheets("L4_Pivot").Protect Password:="bbc2019", AllowUsingPivotTables:=True, UserInterfaceOnly:=True

End Sub

 

[henryjhu]

Does anyone know why?