Pivot Table question

[Beverly Darvill]

Hi

I have spreadsheets that take data via a pivot table from an OLAP cube that
is held on a terminal server.

The spreadsheets hold figures entered by me and figures obtain from the
pivot table.

I want to enable the automatic update on the pivot table but the pivot table
is sorted by a project number obtained from the OLAP cube. My problem is if
a new project is added to the OLAP cube then it is automatically selected
from the drop down list. I want to be able to turn this off so that it only
selects the project I have previously chosed. For example if I have selected
the filter number of 518 and a new project is added to the OLAP cube of
ABC123 when a refresh of the data is enabled it will select both 518 and
ABC123 and I want it to only select 518 on the refresh.

How do I do it please.

Thanks

 

[Shane Devenshire]

Hi,

I don't think there is any built-in way to do this. You could write VBA
code the responds to the Refresh event. But you are going to need to save
the prior setting before triggering refresh and then reapply them after the
refresh.

You will want to look at this event:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

End Sub