I found this solution online, and it addressed this pretty well. My only concern is looping through all the pivots and queries might become time consuming if there's a lot of them:
Sub RefreshTables()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim objList As ListObject
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each objList In ws.ListObjects
If objList.SourceType = 3 Then
With objList.QueryTable
.BackgroundQuery = False
.Refresh
End With
End If
Next objList
Next ws
Call UpdateAllPivots
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Sub UpdateAllPivots()
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub