VBA Filter (with many variables) and copy result to new Worksheets

[Guest]

Using XL 2003 and 97

Two columns (fields) to be used to filter

For Data set #1:

ColumnA
Division
(Choices)
A
B
C

ColumnB
Emp#
(Choices are numbers and ALL need to be considered at once)
From 4142 to 4143
From 4500 to 5001
From 4509 to 4512
From 6000 to 7000
4122, 4125, 4161, 4177
4803

I do know how to VBA-code a two column (field) filter.

That said, how do I handle the multiple ColumnB possibilities? In reality,
there are 23 more Emp#'s than I listed above.

I know that there is a limit (7?) to variables includible in the AND and OR
functions.

There are ten sets of filters like the one above.

After each VBA filter (10 sets) is run, I would like to copy the filtered
data set to a series of new worksheets like:

Data #1
Data #2, etc.

Any help would be greatly appreciated.

TIA

Dennis

 

[Debra Dalgleish]

Set up the criteria area with a blank heading cell, and two heading
cells with "Emp#"

Assuming your first division is in cell C2, enter the following formula
in the cell below the blank criteria heading:

=OR(C2="A",C2="B",C2="C")

Under the first Emp# heading, enter: >=4142
Under the second Emp# heading, enter: <=4143

Copy the formula exactly into the next criteria row (don't drag it down
to copy, or the cell reference will change)
For the Emp# enter the next range of numbers
Continue for all the ranges of Emp#s

To extract the results to a new sheet, start the Advanced filter from
the new sheet. There are instructions here:

http://www.contextures.com/xladvfilter01.html