Pivot Table Automation

[par60056]

I have created several pivot table from a sheet of source data. The pivot tables are the same regardless of the of the source of the data in the source sheet with 1 small exception. For 1 data source certain values are to be excluded.

This is easy enough, just

ActiveSheet.PivotTables("PivotTable2&quot.PivotFields("name&quot.PivotItems("DENVER&quot.Visible = False

Now Denver will never be seen. However when I update the data from Data source 2 I want to see Denver. The obvious thing would be

ActiveSheet.PivotTables("PivotTable2&quot.PivotFields("name&quot.PivotItems("DENVER&quot.Visible = True

and this is what is written if I macro record setting it back to visible. However if you include that in the script you get an error that says "Unable to set the Visible propert of the PivitItem class"

Any hints?

 

[ETID]

Hmmm...maybe this?

ActiveSheet.PivotTables("PivotTable2&quot.PivotFields("name&quot.CurrentPage = "(All)"


Also "name" may be a reserve word in VBA...you might want to consider changing it....I had a problem with "year"

 

[SkipVought]

Hi,
Am I understanding you correctly...

1. You have ONE Pivot Table and

2. You have TWO data sources?

3. You report on EITHER data source 1 OR data source 2 by changing the data range? Skip,
metzgsk@voughtaircraft.com

 

[par60056]

The pivot table is has a data source of columns A-M of the sheet named "rawData" This way the 3 different view in 3 different pivot tables don't need to go back to the source database.

All I have to do is change the single query in the rawData sheet to different time periods or client names and then refresh all the pivot tables. This way all of the pivot tables are always in sync with the same data.

ETID !!! You are Right You win the Prize.

If I change it from "name" to "city" it works.

 

[par60056]

I was wrong. Use of "name" is not the problem.

It did point me in the right direction. When I changed it from "name" to "city" it reset the autosort to manual. With autosort set to manual my original code works.

 

[ETID]

Try doing what you want with the macro recorder on....then you might get a hint as to what syntax that the pivot table is expecting.

 

[par60056]

I recorded what I wanted to do using the macro recorder.

I then ran the macro that it generated and got the same error. If you read my last post, you got me started on the path to enlightenment and I solved the problem.

If you create a pivot table, set the column field to autosort (field properties advanced), then select an item to hide. Now start the recorder, unhide the item and stop the recorder. Rehide the item and run the macro the recorder created. You will get an error when it tries to set visible to TRUE.

The solution is to turn autosort off, turn manualupdate on, then set the items.visible=True, turn manualupdate off and autosort back on. Obvious right? Well documented?

 

[par60056]

I recorded what I wanted to do using the macro recorder.

I then ran the macro that it generated and got the same error. If you read my last post, you got me started on the path to enlightenment and I solved the problem.

If you create a pivot table, set the column field to autosort (field properties advanced), then select an item to hide. Now start the recorder, unhide the item and stop the recorder. Rehide the item and run the macro the recorder created. You will get an error when it tries to set visible to TRUE.

The solution is to turn autosort off, turn manualupdate on, then set the items.visible=True, turn manualupdate off and autosort back on. Obvious right? Well documented?

 

[SkipVought]

It's an undocumented FEATRURE LOL Skip,
metzgsk@voughtaircraft.com