PDA

View Full Version : Hide Any New Column Fields in Pivot Table



HelpNeeded
11-19-2010, 06:05 AM
Hi,

I have a pivot table that reads data from a worksheet. When ever new field items are added to the raw data, after refreshing the pivot tables show these new feilds/items and then I have to go into the pivot column field and deselect these as I only want to show the items that I have already set up in the table, not the new ones.

It have explored various options and it looks like it's impossible to turn off this automatic updating of new fields. However I have found that you can use VBA to unhide fields.

For example, in the table below, the macro hides the columns with the field Kent.


Sub pivot_feild()
With ActiveSheet.PivotTables("PivotTable7").PivotFields("SERVICE")
.PivotItems("Kent").Visible = false
End With
End Sub


Is there a way to specify in the code when updating the pivot table to only show fields listed in the code and ignore all others (I have over 30)?

E.G List these in the code to show as visable by default:

London
Kent
Liverpool
Surrey
Cambridge

Or if there is a simpler solution, I'd be hearing that.

Thanks,

HN