Run VBA after Refresh All with multiple tables

%3CLINGO-SUB%20id%3D%22lingo-sub-3354902%22%20slang%3D%22en-US%22%3ERun%20VBA%20after%20Refresh%20All%20with%20multiple%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3354902%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20want%20to%20have%20a%20block%20of%20VBA%20code%20run%20after%20all%20the%20tables%20in%20the%20workbook%20have%20refreshed%3A%3C%2FSPAN%3E%3C%2FP%3E%3COL%3E%3CLI%3EUser%20clicks%20the%20Refresh%20All%20button%20which%20causes%3C%2FLI%3E%3CLI%3EAll%20tables%20in%20the%20workbook%20to%20be%20refreshed%3CUL%3E%3CLI%3Efour%20tables%2C%20each%20on%20a%20separate%20sheet%3C%2FLI%3E%3CLI%3Etables%20are%20refreshed%20via%20Power%20Query%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3CLI%3EOnce%20all%20tables%20have%20completed%20the%20refresh%2C%20the%20VBA%20code%20runs%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CSPAN%3EI%20have%20accomplished%20this%20in%20a%20different%20project%20using%20WithEvents%20and%20setting%20a%20QueryTable%20variable%20but%20that%20was%20only%26nbsp%3Bone%26nbsp%3Btable%20being%20refreshed.%20I%20just%20can't%20seem%20to%20see%20the%20logic%20to%20extend%20that%20approach%20to%20encompass%20multiple%20tables.%3CU%3E%3CSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FU%3EThe%20code%20used%20for%20one%20table%2C%20set%20in%20ThisWorkbook%3A%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3E%3CPRE%3EOption%20Explicit%0APrivate%20WithEvents%20QT%20as%20QueryTable%0A%0APrivate%20Sub%20Workbook_Open()%0A%20%20%20%20%20%20%20Set%20QT%20%3D%20Sheet1.ListObjects(1).QueryTable%0AEnd%20Sub%0A%0APrivate%20Sub%20QT_AfterRefresh(ByVal%20Success%20As%20Boolean)%0A%20%20%20%20%20%20%20%20If%20Success%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%5BI%5D%5Bblock%20of%20code%20to%20be%20run%20after%20refresh%20completes%5D%5B%2FI%5D%0A%20%20%20%20%20%20%20%20End%20If%0AEnd%20Sub%3C%2FPRE%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3354902%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3355038%22%20slang%3D%22en-US%22%3ERe%3A%20Run%20VBA%20after%20Refresh%20All%20with%20multiple%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3355038%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1386584%22%20target%3D%22_blank%22%3E%40MichaelJSchulz%3C%2FA%3E%26nbsp%3BTry%20replacing%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EPrivate%20Sub%20Workbook_Open()%0A%20%20%20%20%20%20%20Set%20QT%20%3D%20Sheet1.ListObjects(1).QueryTable%0AEnd%20Sub%3CBR%20%2F%3Ewith%3C%2FPRE%3E%0A%3CPRE%3EPrivate%20Sub%20Workbook_Open()%3CBR%20%2F%3EDim%20i%20as%20Long%3CBR%20%2F%3EFor%20i%20%3D%201%20to%20Sheets.Count%0A%20%20%20%20%20%20%20Set%20QT%20%3D%20Sheets(i).ListObjects(1).QueryTable%3CBR%20%2F%3ENext%20i%0AEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20there%20are%20sheets%20in%20addition%20to%20the%204%20sheets%20that%20contain%20the%20tables%2C%3CBR%20%2F%3Eyou%20may%20need%20to%20use%3CBR%20%2F%3EPrivate%20Sub%20Workbook_Open()%3CBR%20%2F%3EDim%20i%20as%20Long%3CBR%20%2F%3EFor%20i%20%3D%201%20to%20Sheets.Count%20%3CBR%20%2F%3E%20%20%20%20If%20Sheets(i).ListObjects.Count%26gt%3B0%20then%3CBR%20%2F%3E%20%20%20%20%20%20%20%20Set%20QT%20%3D%20Sheets(i).ListObjects(1).QueryTable%3C%2FPRE%3E%0A%3CPRE%3E%20%20%20%20End%20if%3CBR%20%2F%3ENext%20i%20%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3358850%22%20slang%3D%22en-US%22%3ERe%3A%20Run%20VBA%20after%20Refresh%20All%20with%20multiple%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3358850%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1225189%22%20target%3D%22_blank%22%3E%40Doug_Robbins_Word_MVP%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20does%20not%20appear%20to%20work%20as%20we%20want.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20I%20correctly%20understand%20what%20is%20happening%20by%20viewing%20the%20Locals%20window%2C%20QT%20holds%20only%20one%20table%20at%20a%20time%20instead%20of%20holding%20multiple%20tables.%3C%2FLINGO-BODY%3E
New Contributor

I want to have a block of VBA code run after all the tables in the workbook have refreshed:

  1. User clicks the Refresh All button which causes
  2. All tables in the workbook to be refreshed
    • four tables, each on a separate sheet
    • tables are refreshed via Power Query
  3. Once all tables have completed the refresh, the VBA code runs

I have accomplished this in a different project using WithEvents and setting a QueryTable variable but that was only one table being refreshed. I just can't seem to see the logic to extend that approach to encompass multiple tables.

The code used for one table, set in ThisWorkbook:

Option Explicit
Private WithEvents QT as QueryTable

Private Sub Workbook_Open()
       Set QT = Sheet1.ListObjects(1).QueryTable
End Sub

Private Sub QT_AfterRefresh(ByVal Success As Boolean)
        If Success Then
              [I][block of code to be run after refresh completes][/I]
        End If
End Sub
2 Replies

@MichaelJSchulz Try replacing

 

Private Sub Workbook_Open()
       Set QT = Sheet1.ListObjects(1).QueryTable
End Sub
with
Private Sub Workbook_Open()
Dim i as Long
For i = 1 to Sheets.Count Set QT = Sheets(i).ListObjects(1).QueryTable
Next i End Sub

If there are sheets in addition to the 4 sheets that contain the tables,
you may need to use
Private Sub Workbook_Open()
Dim i as Long
For i = 1 to Sheets.Count
If Sheets(i).ListObjects.Count>0 then
Set QT = Sheets(i).ListObjects(1).QueryTable
    End if
Next i
End Sub
@Doug_Robbins_Word_MVP

It does not appear to work as we want.

If I correctly understand what is happening by viewing the Locals window, QT holds only one table at a time instead of holding multiple tables.