Selecting a groups of cells using multiple criteria

kmiles

Board Regular
Joined
Apr 1, 2002
Messages
113
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

Board Regular
Joined
Apr 1, 2002
Messages
113
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.
 

Forum statistics

Threads
1,181,658
Messages
5,931,271
Members
436,786
Latest member
Deniel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top