Loop to copy rows of data to another sheet if version number is higher

[mattyfaz]

Hi,

I am having trouble with a macro to do the following.
Search the Version column of the OldSheet for the latest Version. Search the Version column in the NewSheet for the latest Version. Copy any Versions from the OldSheet (and the cells next to it containing text) and insert it in the NewSheet. I have this working easily if the OldSheet is only 1 version higher than the NewSheet, it simply just searched the column for the next blank space and then went up 1 row and copied data. This will normally work fine although sometimes the OldSheet will be more than 1 Version ahead of NewSheet. In this case it only copies the latest (e.g. NewSheet may have up to Version 0.10, but OldSheet goes up to 0.13, the Macro only copies 0.13 so NewSheet would show 0.8, 0.9, 0.10, 0.13). I need it to copy all of the new Versions.

Below is the code, I know I need to put a loop in somewhere but I am having trouble and seem to screw the macro every time I do that. Have attached a worksheet to show what I mean as well.

Please Login or Register  to view this content.

[Richard Buttrey]

This question really should be a FAQ. I see it time and time again and looping just isn't the best way of doing this sort of thing.

Whenever you find yourself creating a loop step back a bit and consider if there isn't a more efficient way. Loops should only be used as a last resort since they can be very slow particularly when many rows are involved.

I believe the fastest and most efficient way of doing this will be to use a Data Filter using the relevant criteria.

That said please add the expected results you want to see so that we can better advise.

[mattyfaz]

That said please add the expected results you want to see so that we can better advise.

Currently NewSheet goes up to 0.10, the Macro I have at the moment will grab the bottom value from OldSheet and insert it at the bottom of NewSheet. The problem is this will mean NewSheet goes from 0.10 to 0.13, it does not copy across 0.11 and 0.12.

I am expecting NewSheet to copy across any data from OldSheet that has a higher Version than itself, so in this example NewSheet should copy across 0.11, 0.12 and 0.13. Hopefully that makes sense.

[mattyfaz]

Tried looking into Data Filters as suggested above and feel even more lost now. Any advice?

[Richard Buttrey]

If you manually create the table of results that you expect to derive and clearly explain in a note how you've arrived at the table, i.e. your rules, then I'll take a look later.

[mattyfaz]

This is how it currently works, NewSheet has up to Version 0.10:
CurrentNew.JPG
When I run the macro it grabs the last figure from OldSheet and adds it to the bottom of NewSheet
OldSheet has up to Version 0.13
CurrentOld.JPG
After running Macro Version 0.13 get's copied to NewSheet but not 0.11 & 0.12
NewNew.JPG

What I want is the Macro to ALSO copy 0.11 & 0.12 but have this occur for any number, e.g. so if NewSheet only went to 0.8 it would copy 0.9 - 0.13 from OldSheet.

Hopefully that makes sense.

Test.xlsm