Error refreshing pivottable with macro, only when code includes protect/unprotect, why?

[wiseone]

Hi All, Strange problem. The below code filters and refreshes a pivot table when the range "ProductionLine" Changes. For some reason, it works fine when there is no protection on the sheet. But when I add sheet protection (protection with no password) and include the unprotect/protect code, I get the following error:

"RefreshTable method of PivotTable class failed" and it highlights the "pt1.refreshtable" line.

Any ideas why the protection is causing this error considering its being disabled while the macro runs?

Thanks in advance for your help!

CODE WITHOUT PROTECTION:

Private Sub Worksheet_Change(ByVal Target As Range)


    'Checks if SKU entry cell has changed.
    If Not Application.Intersect(Range("ProductionLine"), Range(Target.Address)) Is Nothing Then
        Application.ScreenUpdating = False
        
        'Set the Variables to be used
        Dim pt1 As PivotTable
        Dim Field1 As PivotField
        Dim NewCat1 As String


        'Enter Pivot Table name (you can rename it in the Pivot table settings)
        Set pt1 = ActiveSheet.PivotTables("Packaging_Capacity_Pivot")


        'Name Filter.
        Set Field1 = pt1.PivotFields("Slicing Hall")
        
        'What do you want to filter to?
        NewCat1 = ActiveSheet.Range("ProductionLine").Value
                
        'This updates the SKU list Pivot
        With pt1
            Field1.CurrentPage = NewCat1
            pt1.RefreshTable
        End With
      
    End If
    Application.ScreenUpdating = True
End Sub

CODE WITH PROTECTION:

Private Sub Worksheet_Change(ByVal Target As Range)


    'Checks if SKU entry cell has changed.
    If Not Application.Intersect(Range("ProductionLine"), Range(Target.Address)) Is Nothing Then
        ActiveSheet.Unprotect
        Application.ScreenUpdating = False
        
        'Set the Variables to be used
        Dim pt1 As PivotTable
        Dim Field1 As PivotField
        Dim NewCat1 As String


        'Enter Pivot Table name (you can rename it in the Pivot table settings)
        Set pt1 = ActiveSheet.PivotTables("Packaging_Capacity_Pivot")


        'Name Filter.
        Set Field1 = pt1.PivotFields("Slicing Hall")
        
        'What do you want to filter to?
        NewCat1 = ActiveSheet.Range("ProductionLine").Value
                
        'This updates the SKU list Pivot
        With pt1
            Field1.CurrentPage = NewCat1
            pt1.RefreshTable ' THIS LINE HIGHLIGHTS ON ERROR.
        End With
      
    End If
    ActiveSheet.Protect
    Application.ScreenUpdating = True
End Sub

 

[wiseone]

Bump.....Anyone have a thought on this one?

 

[wiseone]

I guess this stumped everyone? ...

 

[Jerry Sullivan]

I've responded to your similar question here...

https://www.mrexcel.com/forum/excel-questions/997329-error-only-when-sheet-protection-why.html