Pivot table- detecting grouped fields...

[Dave Ramage]

Hi

I'm attempting to create a utility that takes a snapshot
of the current pivot table settings, records them (either
in a db or hidden excel sheet etc.), and can then re-apply
them when requested.

I've got stuck when trying to determine if a pivot field
has any grouping applied to it, and if so what are the
grouping parameters.

For example, if the user has grouped pivot
field "Repair_Date" by months and years then an additional
pivot field "Years" is created. The only property I've
found that sets these two grouped fields appart from all
the others is .TotalLevels = 2 (even .ChildItems = 0 for
both). How can I decode the grouping structure of these
two fields?

Cheers,
Dave

 

[Tom Ogilvy]

Just a thought, but wouldn't it be easier to copy the sheet, hide and rename
the original, let the user make changes to the copy.

When ready to restore, delete the new, unhide the old and rename it.

 

[Dave Ramage]

Thanks Tom...an elegant alternative I hadn't thought of. I
guess if the data source is the same e.g. same range on
worksheet then the additional copies won't take up much
extra space/resource.

I'll give Tom's suggestion a try, but if anyone has any
ideas regarding the OP I'd be interested to hear.

Cheers,
Dave