Forum Discussion

MichaelJSchulz's avatar
MichaelJSchulz
Copper Contributor
May 11, 2022

Run VBA after Refresh All with multiple tables

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
Macros and VBA

2 Replies

  • Doug_Robbins_Word_MVP's avatar
    Doug_Robbins_Word_MVP
    MVP
    May 11, 2022

    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
    • MichaelJSchulz's avatar
      MichaelJSchulz
      Copper Contributor
      May 11, 2022
      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.

Resources