I am looking to write a macro that will copy & paste only cells with new data into either the same or another sheet. Here is what I mean:

In Sheet A, all of column A is a list of dates, spanning over 2 years for example. Columns B – H contain data that is updated when I run a web query on a separate page (retrieved using VLOOKUP). However, only the data for the CURRENT WEEK is updated with the query. I have previously copied and pasted special values over itself whenever I update the file, because otherwise when I run it the next week, that data from the previous week would go away.

I have columns B – H (current date & forward) set up so that the data only appears if it is available. Meaning, I use an if(iserror) formula with the vlookup, so all future dates that are not part of the current week show up as a blank cell.

What I want to do is write a macro that will search this page, find all data retrieved from the VLOOKUP (excluding those empty cells – so basically the data from that current week), and copy & paste values EITHER…. 1) back over itself in Sheet A, or 2) into another sheet that stores the values only data. Either one works for me, it is just a matter of whatever is easiest.

Please ask any questions if this does not make any sense. Thank you!