Pivot Table Items

Hi all,

I would like to automate this process described in Debra Link

http://www.contextures.com/xlPivot03.html#Page

The attached file is what I've come up with but it's not very dynamic as if I add items etc it won't update.

Any help/tips would be appreciated

VBA Noob

If you add a couple of dynamic ranges:

"codes": =OFFSET(Pivot!$E$1,1,0,COUNTA(Pivot!$E:$E)-1,2)
"pivotdata": =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),4)

then change the data source of your to be pivotdata, and alter your code to

Please Login or Register  to view this content.

when you add new entries to the Data tab or the list of codes on the Pivot tab and click the button it will refresh everything.


Col

colofnature,

Worked a treat

Thanks

VBA Noob

Did you change the pivot table's data source? (Right click the table, select PivotTable Wizard, go back to step 2 of 3 and put pivotdata in the Range box). That done, when the PivotCache.Refresh is executed the dynamic range is used to rebuild the table, and changeing the status of the PivtItems won't cause an error.


Col

Done thanks.

Instead of listing in the sheet would it be possible to populate a userform to select items to show ??

VBA Noob

Shouldn't be a problem, you could use a variant of one of my favouritest things, John Walkenbach's GetOption form. Change the radio buttons for checkboxes and hey presto...

Or just populate the items into a list-box and return the selected items as an array of row numbers (or the sum of 2^row numbers). That would probably be easier.


C

Thanks

Will have a play

VBA Noob

I had to give this a try...

Please Login or Register  to view this content.

This will use the form from j-walk, modified to use check boxes, to populate the true/false values in your dynamic range "codes".


Col

colofnature,

Prefect thanks.

Only small change I made was to swap the cancel and ok buttons postion numbers.

VBA Noob