Macro that willl affect all Pivot Tables in the workbook

[ddd]

Hi - trying to write macro that will affect ALL existing pivot tables in my workbook (not just the first one) to change their layout from the default Compact to my preference of Tabular layout. The macro below works HOWEVER it changes only the first pivot table in the workbook. Been playing with some For Each loops, but no luck so far....I'm missing something. Need help to write code to convert ALL Pivot tables' compact layout to the Tabular layout for all present pivot tables in the file, please. Thank you!

Please Login or Register  to view this content.

[fredlo2010]

See if this code helps

Please Login or Register  to view this content.

Thanks

[ddd]

Hi fredlo2010 - thanks for your reply! I pasted your code into my macro but nothing at all happens when I run it (it's a global macro-enabled file, so that shouldn't be it). I wonder if the set pt = sh.pivottables(1) part of your suggestion is having an adverse effect (since there are several pivot tables on the worksheet and in the workbook) and the (1) instructs to look at first pivot table?? Thanks again for your assistance

[ddd]

Hi again fredlo2010 - had a chance to further test your code suggestion, and sure enough, it works great IF there is only one pivot table per worksheet in a workbook (if so, each one on each worksheet is successfully converted to Tabular layout). However, if there are 2 or more pivot tables on a single worksheet in the file, the code won't work at all on any of them! Wondering if you might have a suggestion to tweak the code to address ANY AND ALL pivot tables on all worksheets in the file? Thanks again

[fredlo2010]

Hello ddd,

Ok it should be working now

Please Login or Register  to view this content.

[ddd]

Thank you in advance, fredlo2010! Code looks like it's gonna do the job...I will give this a try ASAP and will respond again

[fredlo2010]

hello,

i just realized there was a small flaw in the code

Please Login or Register  to view this content.

[ddd]

Hello fredlo2010 - works wonderfully. I can't thank you enough! Appreciate your time and expertise!!!

[fredlo2010]

You are welcome!

[ddd]

Why this didn't occur to me earlier!?!?! What about adjusting your code to make it affect ALL Pivot tables but JUST ON THE ACTIVE SHEET? In other words, if there is one or more PTs on whatever sheet is currently active, convert their layout to Tabular WITHOUT affecting other PTs that may be present on the other sheets in the file?

[fredlo2010]

This should take care of that (untested)

Please Login or Register  to view this content.

[ddd]

Hi fredlo2010 - thank you! I just tested it and got a compile Block IF error message (removed the last end if just to see, but it's not working). Any further suggestion, please? Thanks again

[fredlo2010]

I just tested it and got no error. Yet my workbook is empty. can you send a screenshot of the error and line. and if possible a small sample of the workbook?

Thanks

[ddd]

Hi Fredlo2010 - I attached a sample test file. There are pivot tables on 2 worksheets. When I run the macro, I want only the PTs on the current active sheet I'm viewing (not the other sheets) to be affected. However, when I run it, I get the following VB error: COMPILE ERROR: END IF WITHOUT BLOCK IF. I've already put the macro in the file so you can run it to test. Many thanks in advance for looking at this

[fredlo2010]

Hi ddd,

Thanks for the sample it makes debugging a lot easier. All done.

Please Login or Register  to view this content.

[ddd]

PERFECT! You've been SO helpful.....Thank you very much

[fredlo2010]

I am glad I was able to help

[EDubs]

I just happened across this thread. I have been looking for something like this for a while. I appreciate the clean and easy to follow code.
Thanks!!