Move row to another sheet when cell has a date

[A.C. Doodle]

Hello

I'm trying to move a record (row) from the "Open orders" sheet to the "Recieved" sheet when I close the file. To logically describe the desired results in as I envision they should occur;

1.Copy row or rows with a date in the cell at column W
2.Insert those rows on the sheet "Recieved"
3.Sort this sheet by JULIAN DATE and SERIAL NUMBER (columns J and K), ascending order
4.Return to sheet "Open Orders"
5.Save and exit the program

I have searched but havent found anything I could modify to fit this task. Any assistance or direction provided will be greatly appreciated.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[A.C. Doodle]

John, thank you. This does exactly what I asked for. Perfectly. I just wasn't as clear as I should have been with what I asked for. For that I apologize.

I need it to delete the copied rows from the sheet "Open orders". Can you also offer advice on making this execute when I exit excel?

[JOHN H. DAVIS]

This revision will delete the copied rows:

Please Login or Register  to view this content.

In the workbook before close event module you can call the module or place the code there; This will execute when you close the workbook. Not sure about how to call the macro when exiting the application itself.

Please Login or Register  to view this content.

[A.C. Doodle]

Awesome! The revision does exactly what we needed. I can now do away with the printed / handwritten version of this we've been maintaining for ages.

Thanks again John. I can't express enough my appreciation for your help.


MSgt. A.C. Doodle, USAF

[JOHN H. DAVIS]

You're welcome Msgt. I'm a former CPO USN. Glad to help out a fellow serviceman.

[bakerman2]

To avoid errors, never delete and shift rows when you are in a loop from top to bottom. You only delete rows from bottom to top.
Suppose you have to process rows 5 and 6, you process row 5 ,delete and shift making row 6 becoming row 5. Since you have already delt with row 5 in your loop you move on to row 6 so forgetting to process the former row 6.
It is therefore better to just clear the contents of the processed rows and in the end delete all the blank cells in columns 1 using Special cells.
Also it isn't necessary to select a sheet to perform a sorting action. Use With .... End With instead.

Please Login or Register  to view this content.