Cut entire row and paste to new sheet if cells and date criteria match

[themansion]

Hi Everybody,

I am working in beautiful island of Bali, and I m trying to improve a macro to ease my friend job. they are currently sorting the data manually, which takes hours, while a great macro could do that in a click. Sadly I dont know how to write this macro... We are seeking your kind assistance!

Below code allow us to sort data with one criteria:

if a cell (in column B sheet 1) contains word "aaa" then cut and paste the entire row into next sheet.

We d like to add one more criteria to this macro.

I will create a new sheet (sheetDATE) where column A contains the dates of the eight first days of the month, then column B contains the next 8 dates and so on, as per below example.

1 3/1/2013 3/9/2013 3/17/2013 3/25/2013
2 3/2/2013 3/10/2013 3/18/2013 3/26/2013
3 3/3/2013 3/11/2013 3/19/2013 3/27/2013
4 3/4/2013 3/12/2013 3/20/2013 3/28/2013
5 3/5/2013 3/13/2013 3/21/2013 3/29/2013
6 3/6/2013 3/14/2013 3/22/2013 3/30/2013
7 3/7/2013 3/15/2013 3/23/2013 3/31/2013
8 3/8/2013 3/16/2013 3/24/2013

The, the macro would then act as follows:

if a cell (in column B sheet 1) contains word "aaa" and date in column H (sheeet1) match a date of column A (sheetDATE), then cut and paste the entire row into next sheet2.
if a cell (in column B sheet 1) contains word "aaa" and date in column H (sheeet1) match date of column B (sheetDATE), then cut and paste the entire row into next sheet3.
if a cell (in column B sheet 1) contains word "aaa" and date in column H (sheeet1) match date of column C (sheetDATE), then cut and paste the entire row into next sheet4.
if a cell (in column B sheet 1) contains word "aaa" and date in column H (sheeet1) match date of column D (sheetDATE), then cut and paste the entire row into next sheet5.
if a cell (in column B sheet 1) contains word "bbb" and date in column H (sheeet1) match date of column A (sheetDATE), then cut and paste the entire row into next sheet6.
if a cell (in column B sheet 1) contains word "bbb" and date in column H (sheeet1) match date of column B (sheetDATE), then cut and paste the entire row into next sheet7.

And so on.

We would really appreciate if you could help us on this!

Please Login or Register  to view this content.

[AB33]

Please attach a sample book. You seem to have a code and you have not mentioned if the code is not working, or would like to improve it.

[themansion]

Hi,

Thank you for you reply.

Enclosed with this post is the excel file with a first code which is working well, but it sorts the data based on one criteria, the customer name.

WHAT IT DOES:

if customer name (column B - sheet1) = "aaa" then cut entire row and paste to sheet2


if customer name (column B - sheet1) = "bbb" then cut entire row and paste to sheet3

etc.

We are trying to make the code cut the row to a selected sheet destination depending on two criteria:
FIRST CRITERIA is the customer name (this works already)
SECOND CRITERIA is departure date (column H - sheet1)


WHAT WE WANT IT TO DO:

if customer name (column B - sheet1) = "aaa" and departure date (column H - sheet1) match to one date of column A sheetDATE (last sheet) then cut entire row and paste to sheet2

if customer name (column B - sheet1) = "aaa" and departure date (column H - sheet1) match to one date of column B sheetDATE (last sheet) then cut entire row and paste to sheet3

if customer name (column B - sheet1) = "aaa" and departure date (column H - sheet1) match to one date of column C sheetDATE (last sheet) then cut entire row and paste to sheet4

if customer name (column B - sheet1) = "aaa" and departure date (column H - sheet1) match to one date of column D sheetDATE (last sheet) then cut entire row and paste to sheet5

if customer name (column B - sheet1) = "bbb" and departure date (column H - sheet1) match to one date of column A sheetDATE (last sheet) then cut entire row and paste to sheet6

and so on..

Many thanks,

Elie

[nilem]

Hi Elie
maybe something like this

Please Login or Register  to view this content.