I have a file I import from Mainframe to Excel. A new request requires that
the data be filtered and split into 6 different sheets. Since I can have up
to 8 criteria requirements on 1 field, I understand I cannot use an Auto
Filter. It looks like I can use the Advanced Filter, but then I need to copy
the filtered data (you can only copy filtered data to the active sheet) to
the original data import sheet instead of immediately copying it to the
individual sheets. That means I then need to copy each individual filtered
range and paste into the specified sheet. Am I understanding this correctly,
and/or is there a better way of doing this? Thanks for any help with this; I
really appreciate it.

That means I then need to copy each individual filtered range and paste
into the specified sheet

There's nothing wrong with doing it that way, but you can get Excel to
directly put the results on the final destination sheet. All you have to do
is start on that final sheet. When you invoke Advanced Filter on the final
sheet you have to click back to the data sheet to select the data range.
But, since you can have the criteria range on the final sheet, you only have
to click back that one time. It might even be handy to have a criteria
range setup on each destination sheet in advance.

--
Jim
"cottage6" wrote in message
...
|I have a file I import from Mainframe to Excel. A new request requires
that
| the data be filtered and split into 6 different sheets. Since I can have
up
| to 8 criteria requirements on 1 field, I understand I cannot use an Auto
| Filter. It looks like I can use the Advanced Filter, but then I need to
copy
| the filtered data (you can only copy filtered data to the active sheet) to
| the original data import sheet instead of immediately copying it to the
| individual sheets. That means I then need to copy each individual
filtered
| range and paste into the specified sheet. Am I understanding this
correctly,
| and/or is there a better way of doing this? Thanks for any help with this;
I
| really appreciate it.

Jim,
Thanks so much. I didn't think about trying to start out on the destination
sheet which works out just fine.

"Jim Rech" wrote:

That means I then need to copy each individual filtered range and paste
into the specified sheet

There's nothing wrong with doing it that way, but you can get Excel to
directly put the results on the final destination sheet. All you have to do
is start on that final sheet. When you invoke Advanced Filter on the final
sheet you have to click back to the data sheet to select the data range.
But, since you can have the criteria range on the final sheet, you only have
to click back that one time. It might even be handy to have a criteria
range setup on each destination sheet in advance.

--
Jim
"cottage6" wrote in message
...
|I have a file I import from Mainframe to Excel. A new request requires
that
| the data be filtered and split into 6 different sheets. Since I can have
up
| to 8 criteria requirements on 1 field, I understand I cannot use an Auto
| Filter. It looks like I can use the Advanced Filter, but then I need to
copy
| the filtered data (you can only copy filtered data to the active sheet) to
| the original data import sheet instead of immediately copying it to the
| individual sheets. That means I then need to copy each individual
filtered
| range and paste into the specified sheet. Am I understanding this
correctly,
| and/or is there a better way of doing this? Thanks for any help with this;
I
| really appreciate it.