Pivot Table Runtime Error

[All2Cheesy]

Hi all, this is a bit of a long one, so you're going to need to bare with me for a bit.

I have some code which locks every sheet in a workbook simultaneously listed here:

Sub LockAll()

'Disable additional features
With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        .EnableEvents = False
        .DisplayAlerts = False
End With

    Dim S As Object
    Dim pWord1 As String, pWord2 As String
    pWord1 = InputBox("Please Enter the password")
    If pWord1 = "" Then Exit Sub
    pWord2 = InputBox("Please re-enter the password")
     
    If pWord2 = "" Then Exit Sub
     'make certain passwords are identical
    If InStr(1, pWord2, pWord1, 0) = 0 Or _
    InStr(1, pWord1, pWord2, 0) = 0 Then
        MsgBox "You entered different passwords. No action taken"
        Exit Sub
    End If
    For Each Worksheet In Worksheets
        Worksheet.Protect Password:=pWord1
    Next
 
 'Enable additional feautres
With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic
        .EnableEvents = True
        .DisplayAlerts = True
End With
 
 End Sub

This works fine, however, once I've run this code, 3 of my 4 pivot tables will no longer refresh until I have closed and reopened the workbook. The code I'm using to refresh my pivot tables are identical, save for the name of the pivot table in each. See below:

Sub UpdateDataPivot()

'Disable additional feautres
With Application
        .ScreenUpdating = False
        .Calculation = Manual
        .EnableEvents = False
        .DisplayAlerts = False
End With

'Unlock Sheet
ActiveSheet.Unprotect Password:="PASSWORD"

'Update Pivot
    Range("B48").Select
[COLOR="#FF0000"]    ActiveSheet.PivotTables("DataSample").PivotCache.Refresh[/COLOR]
'Return to top
    ActiveWindow.ScrollRow = 1
    Range("A1").Select
    
'Enable additional feautres
With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic
        .EnableEvents = True
        .DisplayAlerts = True
End With

End Sub

Trying to refresh the tables nets the following error:

Runtime error 1004:
Cannot edit pivot table on protected sheet

Clearly, the sheets are unprotected when refreshing. The line highlighted in red is where the debugger stops. This is driving me mad as one of my tables stills works perfectly fine despite having almost identical codes. Again, sorry for the long post but help would be appreciated.

 

[All2Cheesy]

EDIT: I've fixed the pivot table affected by the above Macro, turns out it was having a different issue, where the data source referred to the same data source as another table. (Fixed by changing the data source). However, I am still facing the above issue with my two remaining tables.

Sub UpdateVarPivot()

'Disable additional feautres
With Application
        .ScreenUpdating = False
        .Calculation = Manual
        .EnableEvents = False
        .DisplayAlerts = False
End With

''Unlock Sheet
ActiveSheet.Unprotect Password:="PASSWORD"

'Update Variance Analysis
    Range("B48").Select
    ActiveSheet.PivotTables("Variance").PivotCache.Refresh



'Update Variance Analysis - Incumbent
    Range("G7").Select
    ActiveSheet.PivotTables("Incumbent").PivotCache.Refresh


    'Return to top
    ActiveWindow.ScrollRow = 1
    Range("A1").Select

'Enable additional feautres
With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic
        .EnableEvents = True
        .DisplayAlerts = True
End With
End Sub

 

[All2Cheesy]

EDIT: EDIT:

Turns out I've gotten it all sorted. Although I was getting two different error messages, the same fix applied.

Changing the data source of my PIVOT tables so they were not identical to that of another PIVOT table seemed to be the fix.