VBA to compare 2 data outputs and select only lines with changes

[rpinxt]

Ok I will try to keep it simple (workbook attached).

I have "old" data on sheet1.
Then I run the same report once more (next month) and put that data on sheet2

Now what I need is that sheet2 will be compared with sheet1.
And the lines that have changes in them (and that can be in any column!!) should be copied to sheet3.

I marked on sheet2 the lines which the VBA code should find because the cell I changed is highlighted in yellow.

Hopefully this somewhat makes sense my request

[PCI]

See how next code can help

Please Login or Register  to view this content.

[rpinxt]

Thanks for this.
I must say I works just fine in the example I gave.
However unfortunately it does not work anymore when both sheet don't have exactly the same format.
In other words when row 2 in sheet 1 is row 5 in sheet 2 this does not work anymore.

I suppose with a sort on both sheets one could fix this but on sheet2 there could also be new additional lines which were not yet existing in sheet1.
So in that case sheet2 would be longer and at a certain point sheet1 and 2 would not by synchrone anymore on a row level.

Is there a way how this could be tackled?
For instance could you lookup an entrire row of sheet2 based on the Key field (column A) and then check if it is different in sheet1?
If different then copy it to sheet3 and also if it does not exist (so if it is new) then also copy to sheet3.

Or maybe that is just to complex for excel vba?

[PCI]

Is it what you need??

Please Login or Register  to view this content.

[rpinxt]

Wow...this looks very promissing
I do not fully understand the code but this does indeed what I would expect.
Now I will try to incorporate it in my model.
Thanks PCI !

[rpinxt]

Ok I incorporated into my model now.
Works ok but just a one thing....
New lines on sheet2 are not noticed.
So if it finds a row on sheet2 which is not on sheet1 it also should copy that line to sheet3

Is there a way to alter the code for that PCI?
Thanks very much

[PCI]

Here it is, I hope.

Please Login or Register  to view this content.

[rpinxt]

Hmm not realy.
Now it takes everything

[PCI]

It should take from sheet2, all new and modified records... is it?
See with the file attached else send a file showing the results you want.

[rpinxt]

Yes that is what it should. The example is good.
However when I try it in my model the code gives an error.
Be aware in my Model Sheet1 of the example is Sheet4 so I changed that.

This is the code:

Please Login or Register  to view this content.

This lines gives the error :

Please Login or Register  to view this content.

[PCI]

What sort of error is it?
The number of columns (NbCol = 13, until M) is correct?
Is it possible to have a more complete sample file closer to the one you are using, to reproduce the issue.

[rpinxt]

Yes the sample data is exacly the same as the real data (only less rows).
I will post the code which comes before your code.

And as said the Sheet2 and Sheet3 are the same only Sheet1 of the Example is in the real model on Sheet4.
Sub CustomerUpdate()

Please Login or Register  to view this content.

So that Call CopyChangeData is where it calls your VBA code.
Maybe something with the position where the macro is before it reaches your piece of code?

The error is : Run-time error '13': Type mismatch

[PCI]

I rebuilt a little bit to call the sub by a main subroutine: No change
Does it fail when you call sub CopyChangedData itself: Without to be called by another one?

[PCI]

The story is very, very, very simple:
There is no difference between sheets and it was not prepared for such configuration ...
Change

Please Login or Register  to view this content.

by

Please Login or Register  to view this content.

and to complete the check do a small change in one sheet just to see

[rpinxt]

Ahh... Thank you!
Sorry my bad....I had 2 different lists but when the entire process is done the new list becomes the old list....
So if I run then after that with the same download there are no differences...

But anyway good thing to have noticed because it could be a plausible scenario of no changes during a month!
Now it looks like it's working just fine
Thanks again for all you help!
Much appreciated.