Expand & Collapse Pivot Tables in multiple worksheets

Hi,

I have 5 worksheets, 1, 2, 3 4 & 5
all have identical Pivot Table columns A, B, C, D & E

is there a way, using VBA, to expand or collapse the same column, say C, in all 5 worksheets at the same time instead of going into each one manually?

Thanks

Hi,

In Excel 2010, Pivot Tables now come with Expand / Collapse buttons.

HTH

Thanks

Hi Rory,

Is there a way to use an event so when a use expands a Pivot Table on Sht 1, Col C Row 3, that exact same location in Shts 2, 3, 4 & 5 will also be expanded at the same time. OR collapse depending on what the User did.

I hope I am making sense.

Thanks

Oh no!

Is a way to use ActiveCell and perform the same tasks in the subsequent shts?

Thanks

Hey Rory,

That's it! You're a Genius!

Thank you! Thank you!

Please may I ask you to tell me what each line of your code does, so I can understand it?


Thank you once again for your assistance.

Thank you for going to all the trouble.

I wouldn't have known where to begin.

TY !

Hi Rory,

I need your assistance once again, to add something to your code, because I am getting a "Type mismatch" error when I refresh a Pivot Table

Something like, if pt.RefreshTable exit sub. My novice attempt

Thank you!

Taking a look at your code, my thoughts are >>
Your code is looking for a ptField and if it's not "On Error GoTo ....". There will always be a ptField but the action I am performing is a Refresh not Expand / Collapse. Should the "On Error GoTo ...." be looking at something else? Am I heading in the wrong direction?

The workbook is massive, I will put together a smaller version and post tomorrow.

Anywhere in the Pivot Table.

Mmm!

Ignore me Rory, I have just tried Refresh again and it worked, no "Type Mismatch" error.

Thanks

pt file.xlsm

Hi Rory,

I get that "Type mismatch" error intermittently :o/

Please see attched, I hope you will be able to solve this

Much appreciated!

YES!

Thanks Rory, I think that's it!

Thank you !

I've created a Pivot in excel 2010, and it has expand collapse buttons (+ and -, screenshot attached). Now if someone opens this pivot in excel 2003, the '+' and '-" sign completely disappears. Is it possible to keep the expand collapse buttons intact in excel 2003 by doing some coding or by any option available within excel..
I've hidden the contents of cells for the obvious reasons.
thread.JPG

Hi Rory,

I have multiple pivots in a single worksheet. the data source is an OLAP cube. I cannot make this macro run on that excel. Can you please help me with that.

Thanks in Advance

A.