Excel spreadsheet that updates financial data via a query every 1 to 15 mins then copies to another sheet which collates this data when it changes

Question

I would really appreciate some help with this project that i am looking to start, i have a worksheet that uses a query which updates every 1 to 15 mins from a financial instruments prices website once this has updated every 1 to 15 mins i would like the data to be automatically saved to another sheet where it is collated in a new column each time so a record of each update is maintained.

i am happy to supply the sheet with the query already setup.

I've tried to find something online for this but it seems that although pieces of this request exist there is no answer to all of this question~!!!

Thanks in advance would be a real life saver!!

Jamie

Answer 1

Assuming this is a webquery that outputs its results to a normal Excel range and not to a range "formatted as table", Assuming the query is on "Sheet1" and the table is named "Table1", Assuming the copy must go to Sheet2, column A. Paste this code in ThisWorkbook:

Option Explicit

Private WithEvents moQ As QueryTable

Private Sub moQ_AfterRefresh(ByVal Success As Boolean)
    If Success Then
        moQ.Destination.CurrentRegion.Copy
        With ThisWorkbook.Worksheets("Sheet2")
            .Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
        End With
    End If
End Sub

Private Sub Workbook_Open()
    Dim olo As ListObject
    Set moQ = ThisWorkbook.Worksheets("Sheet1").QueryTables(1)
End Sub