Save old data when web query refreshes

This is how I would do that.

1) Create a sheet called ARCHIVE and put your titles in A1:F1 to match your query titles
2) Right-click the sheet tab of your QUERY sheet and select VIEW CODE
3) Paste in this event macro:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, MyRNG As Range, DateFIND As Range

On Error Resume Next
Set MyRNG = Range("A:A").SpecialCells(xlConstants, xlNumbers)
If MyRNG Is Nothing Then Exit Sub

With Sheets("Archive")
    For Each cell In MyRNG
        Set DateFIND = .Range("A:A").Find(Format(cell, cell.NumberFormat), LookIn:=xlValues, LookAt:=xlWhole)
        If DateFIND Is Nothing Then
            cell.Resize(, 6).Copy .Range("A" & .Rows.Count).End(xlUp).Offset(1)
        Else
            Set DateFIND = Nothing
        End If
    Next cell
    .Range("A:F").Sort Key1:=.Range("A2"), Order1:=xlDescending, Header:=xlYes
End With

End Sub

4) Close the VBEditor and save the workbook as a macro-enabled workbook (*.xlsm)

Now any time there is a change of anykind on the QUERY sheet, all the NEW data is checked and transferred to the Archive sheet to keep a permanent record. We do this as the data comes in rather than as it is erased.


You can test this out by deleting a few rows on the the Archive sheet, then go back to the Query sheet and edit any cell off to the right, then check the Archive, the missing values will be back.