Thread: Save old data when web query refreshes

Hello there,

After reading one of your old posts - http://www.excelforum.com/excel-prog...refreshes.html - you've managed to help me out. I used your solution in post #11.

 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LC as Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        Application.EnableEvents = False
        Range("A1:A10").Copy Cells(1, LC + 1)
        Application.EnableEvents = True
    End If
End Sub

My question to you is; Instead of copying the old information to the right in the next column...is it possible to copy the old information below, to the next available row? So all information appears in the same column?

I'm not sure how to write the code for that.

Your help would be greatly appreciated.

Thanks,

Blake

hi Blakely, welcome to the Forum. I'll take a look at your query, but you'll need to glance at the Forum Rules (link above) and edit your post above to follow. You need to add CODE tags around the code in that post the way I showed you previously.

It's also demonstrated below in my signature. Go ahead and fix that while I look at your question. Thanks.

Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        Application.EnableEvents = False
        Range("A1:A10").Copy Range("A" & Rows.Count).End(xlUp).Offset(1)
        Application.EnableEvents = True
    End If

End Sub

to prevent copying the webquery link:

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.enableevents=false

  If Not Intersect(Target, Range("A1:A10")) Is Nothing Then cells(rows.count,1).end(xlup).offset(1).resize(10)=range("A1:A10").value

  Application.enableevents=true
End Sub

Let's see your workbook with the version of the macro you're currently using. Your workbook should clearly demonstrate the results you're trying to achieve manually mocked up if necessary.

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.

A more sophisticated approach using a class module to intercept the event Query_afterrefresh:
See also the attachment

Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
  With Sheets("Query").Cells(1).CurrentRegion
     Sheets("Archive").Cells(1).Resize(.Rows.Count, .Columns.Count) = .Value
  End With
    
  MsgBox "Mooi zo"
End Sub

Carlitos, welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.