Pulling my hair out

Hello all,

I've been reading the posts here and have learned TONS about macros, but I'm
still terribly unfamiliar with them. I'm not even sure if I need a macro for
what I'm trying to do.

Basically I have the following worksheet:
Show Title Date Store Amount V, P, O? Department
Macbeth 2-Jan Shoppers $18.75 visa wardrobe
Corgy and Bess 4-Jan Value Village $12.49 petty cash props
5 women 7-Jan Value Village $18.76 other wardrobe
Oklahoma 3-Feb Who cares? $46.98 visa props
Oklahoma 6-Feb Home Depot $56.75 petty cash paint
5 women 18-Feb Home Depot $100.48 other set
5 women 3-Mar Home Depot $22.75 visa set
Macbeth 20-Feb Home Depot $45.62 petty cash set
Corgy and Bess 19-Mar Christie's $29.75 other lighting
Macbeth 18-Jan Shoppers $12.87 Visa Wardrobe

Show title has a drop-down menu because there can only be up to 7 shows in
this season. V, P, O? is the form of payment (only 3 options) and
Department only has 8 options. There could be hundreds of entries in a
season.

I need to filter for columns A and F (Show title & Department) and copy only
columns B thru E onto another sheet. Lookup didn't seem to work for me
because I have 2 criteria in different columns and I don't want the entire
row pasted.

I tried using the AdvancedFilter Method, but I can't seem to program it so
it will only return data from B through E without returning A and F.

Anybody have any suggestions? Any help you could provide would be much
appreciated!

I would probably be inclined to use a pivot table. That will allow you to use
multiple critera and return your data in heirarchical groupings... Place your
cursor anywhere in the data and select Data -> Pivot Tables... follow the
Wizard. Place your Show Title and Department in the top section of the pivot
and add the amount to the center. Place the other fields in the column and
row sections. Move things around until you get them just right. You can also
group on your date field to aggregate the data by month, quarter, year...
--
HTH...

Jim Thomlinson


"Smangler" wrote:

> Hello all,
>
> I've been reading the posts here and have learned TONS about macros, but I'm
> still terribly unfamiliar with them. I'm not even sure if I need a macro for
> what I'm trying to do.
>
> Basically I have the following worksheet:
> Show Title Date Store Amount V, P, O? Department
> Macbeth 2-Jan Shoppers $18.75 visa wardrobe
> Corgy and Bess 4-Jan Value Village $12.49 petty cash props
> 5 women 7-Jan Value Village $18.76 other wardrobe
> Oklahoma 3-Feb Who cares? $46.98 visa props
> Oklahoma 6-Feb Home Depot $56.75 petty cash paint
> 5 women 18-Feb Home Depot $100.48 other set
> 5 women 3-Mar Home Depot $22.75 visa set
> Macbeth 20-Feb Home Depot $45.62 petty cash set
> Corgy and Bess 19-Mar Christie's $29.75 other lighting
> Macbeth 18-Jan Shoppers $12.87 Visa Wardrobe
>
> Show title has a drop-down menu because there can only be up to 7 shows in
> this season. V, P, O? is the form of payment (only 3 options) and
> Department only has 8 options. There could be hundreds of entries in a
> season.
>
> I need to filter for columns A and F (Show title & Department) and copy only
> columns B thru E onto another sheet. Lookup didn't seem to work for me
> because I have 2 criteria in different columns and I don't want the entire
> row pasted.
>
> I tried using the AdvancedFilter Method, but I can't seem to program it so
> it will only return data from B through E without returning A and F.
>
> Anybody have any suggestions? Any help you could provide would be much
> appreciated!

The problem with using a pivot table is that it won't simply list the values
- it wants to aggregate them. I'm already using one in another part of the
workbook for that very purpose

I just need to copy the middle columns based on what's in the department and
show title columns and paste it on a different worksheet.