excel vba pivot table refresh error

Question

Good day,

I am trying to refresh a table on my sheet followed by refresh of the pivot table through the use of VBA

Everything runs good and well, except the part where i try to refresh to pivot, more specifically with the " pt.RefreshTable" code

Sub Refresh()

MSG1 = MsgBox("Are you Connected to (local) Network?", vbYesNo, "?")
If MSG1 = vbYes Then

MsgBox "Refresh in Progress"
Workbooks("Sharepoint Dispute Management Dashboard").Worksheets("Dispute Data").Activate
ActiveSheet.Range("A4").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

'====================='
'    Unlock Sheets    '
'====================='
Workbooks("Sharepoint Dispute Management Dashboard").Worksheets("Dash - 1").Activate
Workbooks("Sharepoint Dispute Management Dashboard").Worksheets("Dash - 1").Unprotect Password:="n"
'====================='
'      Initialize     '
'====================='
Dim pt As PivotTable
'====================='
' Refreshing Dash - 1 '
'====================='
Set pt = Workbooks("Sharepoint Dispute Management Dashboard").Worksheets("Dash - 1").PivotTables("Dash1-Resolved")


 pt.RefreshTable

'====================='
'      Lock Sheet     '
'====================='
 Workbooks("Sharepoint Dispute Management Dashboard").Worksheets("Dash - 1").Protect Password:="n", AllowUsingPivotTables:=True


Else
MsgBox "You can still use the dashboard but the numbers will not be updated" & vbNewLine & vbNewLine & vbNewLine & "To get the latest update, do the following:" & vbNewLine & vbNewLine & "1- Please connect to the local network or through VPN " & vbNewLine & "2- Click (REFRESH DATA)"

End If
End Sub

I receive a "Run Time Error - 1004". Which is object related. I did however try to initialize all the objects but no use — Nadz

Nadz Nadz · Accepted Answer · 2014-12-09T08:35:36

Apparently i had my other worksheets locked, which contained other pivots linked to the one i am trying to refresh. All i did was added a code to unlock all the worksheets in my workbook, refresh the pivot, and lock them again. Worked like a charm.

Hope this comes in handy for anyone facing a similar issue in the future

excel vba pivot table refresh error

1 Answers