match rows and archive, put non matching rows in active

[intothewild]

Hi..first post here ..im quite new to vba and have lost my way a bit, can you help? I need to put together a macro that compares
a pair of similar worksheets. Ive read lots of similar posts but havnt had any luck adapting the advice i found about similar cases:

• Each day I produce a report which has 4 columns.
• I copy and paste each report into a single workbook but put them on different worksheets which are named 'today' and 'yesterday'.
• I want to compare each row on the 'today' worksheet with each row on the 'yesterday' worksheet.
• The entries in each field are alpha numeric and can have spaces and other characters.
• If all the cells in any individual row on the 'today' report do not match exactly any row on the 'yesterday' report then i need the macro to cut and paste this row from worksheet 'today' into another worksheet called 'active'.
• If all the cells in any individual row on 'today' report match exactly any row on 'yesterday' report then i need the macro to cut and paste this row into a worksheet called 'archive'.

I’ve attached an excel file with some example data in it.

The macro needs to be able to cope with assorted data in each cell and also the reports have different numbers of rows each day.
The number of columns and the titles of each column always remain the same.

[rylo]

Hi

Think I have this the right way round.

Please Login or Register  to view this content.

rylo

[intothewild]

Hi

Think I have this the right way round.

Please Login or Register  to view this content.

rylo

...its the right way round...and a very neat solution too, thanks Rylo!

What would be really handy is if for audit purposes i could add another column with the date and time (in this format... 28/06/2011 07:16) into each column E of 'yesterday' and 'today'. The problem is that none of the rows would match (or be unmatched) as the dates would be different, therefore they'd need to be in the sheet but ommitted from the filtering operation.

I can see that the 'working out' would be moved over to column F (from E) but im usure how to proceed given that in the adapted code id only be filtering columns A to D whereas id want to cut and paste columns A to E to include the date.

How would i adapt the code above?

[rylo]

Hi

Here 'tis

Please Login or Register  to view this content.

It does assume that you will be putting a heading into column E for all sheets.

rylo

[intothewild]

Rylo thanks very much. Tidy solution, took me a while to understand but once i got past...

Please Login or Register  to view this content.

..i was there

Im new to vba (but not to programming) and the solution took me by surprise. Do you know of any good places i can look for further info on both basic and advanced Data Management specific techniques? or good posts on the forum (or even how to find them myself which functions/search terms etc should i be thinking about?)

[rylo]

G'day

That question gets asked a lot here. I find that sites like this are really good learning grounds - look at the question posts and solutions and pull them apart. I've learnt a lot that way.

You could try searching the site for answers to questions like yours. I also find that Google is a good way to do the searching - just specify that you are to search this site.

Pretty generic reply I know, but it really is one of those open questions.

rylo