Try to show / hide itmes of a pivot table

[Michael2611]

Hello,

I have a created a pivot table. The data source is an Excel sheet in the same workbook. I set it form A1 to F300. I do not want to show the (blank) item in the pivot table so I unchecked that box. So far so good. But now if I enter a date in the data source the pivot table does not show that either. So I wrote a small macro:

Please Login or Register  to view this content.

If I have that loop active I get the error "Unable to set the Visible property of the PivotItem class".

Is there anything wrong with that code? I replaced .PivotItems("(blank)") with several other values of the PivotItems list (e.g. .PivotItems("4/1/2013") )and get the same error. Only .PivotItems("(blank)") works.

Any ideas? I googled that problem already but did not find a solution.


Regards,

Michael

[Cheeky Charlie]

You can:

1. Record a macro with you setting the visibility of the items one by one, therefore retrieving the syntax which will work.
2. Use a 'dynamic named range' for your pivot source (Google it) and leave the selection as 'all items', including blanks (which there won't be any).

I prefer 2.

[Michael2611]

Hi CC,

1. I did this for one item, then changed that item manually and ran the macro. Same error!!
2. Great idea! Works!

So I prefer 2, too.

[Cheeky Charlie]

Good. Thanks for following up.