Selecting a groups of cells using multiple criteria

[kmiles]

I need to copy cells A - D based on the DIV and PRODUCT, then paste each set to another sheet . I already have code that loops thru and does this by evaluating each DIV and PRODUCT.

Isn't there an easier way to select matching cells when the product changes or the division changes? Where all 1 - A copies to Sheet1, then 2 - A copies to sheet1, then all 2 - B copies to sheet1, etc.

The reason for copying sections at a time is that each section in the destination sheet will be cleared before each paste. I'm handling several hundred thousand transactions that are in multiple worksheets/books. This routine will allow me to copy the data to Access (this is already working).

A B C D
DIV PRODUCT COST SALE DATE
1 A 5.00
1 A 6.00
2 A 7.00
2 B 6.00
2 B 7.00
2 C 8.00
3 A 5.00
3 A 6.00

 

[Datsmart]

I would suggest looking at Advanced Filtering to do what you are after.
Good example of Advanced Filter steps here:
http://www.contextures.com/xladvfilter01.html

Advanced Filter allows you to preset filter options.
It also allows you to filter by criteria in ways standard autofilter can not do. You can choose an export location in the dialog box or in VBA code.
Once you setup the code and criteria, you can use a macro button to copy data from one page to the other.

 

[kmiles]

Thanks Datsmart.

I have successfully used Adv Filter for similar applications before. However, I was specifically interested in the possibility of using one select statement with multiple functions to accomplish the group selection.