VBA to paste new data into another sheet

[TweetingCynical]

Hi All,

First timer here...

Long story short. I have a sheet called Raw Data, which is essentially data copied my account on an online service in the form of a table (they've removed the csv download, so have to copy, but hey ho).

I am trying to automate some of the cleaning process for moving this data into a sheet where I actually need it. In column K of the raw data sheet, there is a unique reference called Order#. Basically, after I've pasted the new data into the raw data sheet, I need to complete the following steps to move the correct new data into the Cleaned Data sheet as follows:

1) Filter the Raw Data sheet to show only "Market" or "Rebate" in column Q
2) Check for any filtered rows in Raw Data that do not yet exist in Cleaned Data, using the Order# as a unique reference
3) Copy those new data rows
4) Paste them at the bottom of the Cleaned Data sheet starting from column A

There is a way I have thought of to do it, which is to delete all data in the Raw Data sheet before pasting in from the website, meaning all data in this sheet will therefore be new ready for the copying to Cleaned Data, but I'd rather retain the information in Raw Data just in case after cleaning there is an error that I need to go back to the raw data to fix.

Can my stepped procedure above be done? If so, any pointers would be great thanks.

Many thanks,

Jon

 

[TweetingCynical]

Ok, so the point where I'm up to....

I decided that a lookup is completely unnecessary really, as all I need to do is number the rows of data in Raw Data, and then past that number into the Cleaned Data with it, then use the find max function to find the highest number in the column in Cleaned Data, then filter the Raw Data (after pasting new data) by everything greater than the max in the Cleaned Data sheet. (This makes sense to me).

I've got all of that working in Excel/VBA no problem...

The issue is now, how do I tell VBA to copy the visible rows in the auto filtered data in the Raw Data sheet, because if I use a cell reference in the via, that cell might not be visible when the new filter is created. For example, when I ran the filter once, A4 was the first visible cell. The next time it was A3, and the next was A7. So how do I tell it to copy the visible data?

Thanks

Jon