I'm trying to refresh a Power Query & then a Pivot Table (running off that data) & I can get it to work when I step through the code using F8 but it will not work when running the macro
Any ideas?
Thanks!!
Sub Test()
Dim WB As Workbook
Dim pt As PivotTable
Set WB = ThisWorkbook
Set pt = ActiveSheet.PivotTables("PivotTable1")
'Refresh Power Queries & Pivot Tables
ThisWorkbook.RefreshAll
WB.Worksheets("By Customer Pivot").Activate
pt.RefreshTable
End Sub
This is how you can refresh all Power Queries.
Sub RefreshAllPQ_Queries()
Dim Connection As WorkbookConnection
Dim lTest As Long
On Error Resume Next
For Each cn In ThisWorkbook.Connections
lTest = InStr(1, cn.OLEDBConnection.Connection, _
"Provider=Microsoft.Mashup.OleDb.1")
If Err.Number <> 0 Then
Err.Clear
Exit For
End If
If lTest > 0 Then cn.Refresh
Next cn
End Sub
And this is how you can refresh a pivot table.
Sub RefreshPivotTable()
Dim wb As Workbook
Dim ws As Worksheet
Dim strWS As String
Dim strPT As String
strWS = "Invoice"
strPT = "pt_Invoice"
Set wb = ThisWorkbook
Set ws = wb.Worksheets(strWS)
ws.PivotTables(strPT).PivotCache.Refresh
Set ws = Nothing
Set wb = Nothing
strWS = vbNullString
strPT = vbNullString
End Sub
