May 10 2022 07:53 PM
I want to have a block of VBA code run after all the tables in the workbook have refreshed:
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
May 10 2022 09:16 PM
@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
May 11 2022 09:15 AM