"Pushing off" oldest data

[dagindi]

I know the title is pretty vague but I honestly couldn't find a better description.

Attached is a sample workbook. It tracks the last 4 purchases of an item. My goal is to have a macro that "pushes" anything in the new order columns to the LAST ORDER columns, LAST ORDER column data to HISTORY 2 columns, HISTORY 2 to HISTORY 3, HISTORY 3 to HISTORY 4 and push off or delete the data in HISTORY 4.

I put a color coded before and after on the sheet showing the movement of the data across the sheet.

The kicker is that the code has to be row specific so that it only moves and deletes the rows that contain new orders.

Any bit of help would be greatly appreciated!!

[scott.s.fower]

i think that should be pretty easy as long at the format doesn't ever change, ie add a History 5 or delete history 4 (although it can easily be modified if that happens, give me 10-20 mins

[scott.s.fower]

Push Off Example.xlsm

there you go, you can try it on Sheet2, I just copied the BEFORE section to that page so you can check to see if it works the way you were hoping

[rollis13]

A little tweak to scott.s.fower's code.
Change:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

[scott.s.fower]

You dont need to use the tweak since you may want to insert header linesif it looksfor the bold header then you can change the layout at will, but its up to u

[rollis13]

Right, but the loop has to be stopped anyway because row 1 has merged cells and my Excel 2003 blows.
Well, this will do to:
Change:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.