Pivot Table Macro Creation

[Randy]

Hello,

I have 2 questions on Pivot table creation with macros.
1) Is there a way to restrict the size of the pivot table
based on one field- before it creates it?

By that I mean - I create a huge list of names, roles,
and hours. If I want to filter to a certain role, it
seems to want the entire table built first (takes 10
minutes), and then removes the unwanted roles. Is there
a way to define the role before the table is created to
save time?

2) For the same table, if, in the source data, someone
adds a new role, it is by default added to the pivot
tables role field as "visible". Is there a way to make
all roles outside of the one I want - not visible?

By that - say I want the role "painter" to be visible,
but I don't want "builder" or "architect". I hardcode it
that way. Then someone adds janitor, and they start
appearing in my painter pivot table.

Any help would be great!

Thanks,
Randy

 

[BrianB]

It seems strange to me that you make a new pivot table each time. I us
these extensively, setting them up manually with a 1 line macro t
Refresh.

Use a dynamic named range like this which will change according to th
number of rows. Insert/Name/Define, type a name, and use a formula lik
this (which uses 10 columns). Use the name as data source for the PT.

= OFFSET(Mysheet!$A$1,0,0,COUNTA(Mysheet!$A:$A),10)

If you have the role field as a Page Field you will be able to selec
the role you want to see. If you want to see several it is worthwhil
having another column in your base data to indicate the ones you wan
to see and using that as a page field

 

[Randy]

Thanks Brian.
Yes, I actually have the named range. What I meant is
that I create new tabs each time. I'll try role as a
page field, and if that fails, I'll add the column you
mention.
thanks for the tips!
Randy