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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

to prevent copying the webquery link:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.