Prevent new pivot table items from being selected

I have a pivot table that contains accounts and reports sales by account.
Users selected the accounts they want to see and save their spreadsheet. When
new accounts are added to the accounts list, users do not want to see the new
accounts. They only want to see the accounts they selected from a previous
session when they refresh the pivot table.

Does anyone know how to prevent the new accounts from appearing in the pivot
table?

Thanks!

Tom,

Use another column: Include, with values of Yes or No, and include it in your pivot table, showing
only Yes.

Then when new data is added, enter No into that column for the new accounts, and it won't affect the
current pivot table.

HTH,
Bernie
MS Excel MVP


"Tom" <[email protected]> wrote in message
news:[email protected]...
>I have a pivot table that contains accounts and reports sales by account.
> Users selected the accounts they want to see and save their spreadsheet. When
> new accounts are added to the accounts list, users do not want to see the new
> accounts. They only want to see the accounts they selected from a previous
> session when they refresh the pivot table.
>
> Does anyone know how to prevent the new accounts from appearing in the pivot
> table?
>
> Thanks!

Thanks for the reposnse Bernie, but we have many spreadsheets deployed out to
users already, and more could be created by them. I'm looking for a better
way to do this without adding new columns that users do not want to see. Can
this behavior be overridden somehow? Is there another way to do this?

"Bernie Deitrick" wrote:

> Tom,
>
> Use another column: Include, with values of Yes or No, and include it in your pivot table, showing
> only Yes.
>
> Then when new data is added, enter No into that column for the new accounts, and it won't affect the
> current pivot table.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Tom" <[email protected]> wrote in message
> news:[email protected]...
> >I have a pivot table that contains accounts and reports sales by account.
> > Users selected the accounts they want to see and save their spreadsheet. When
> > new accounts are added to the accounts list, users do not want to see the new
> > accounts. They only want to see the accounts they selected from a previous
> > session when they refresh the pivot table.
> >
> > Does anyone know how to prevent the new accounts from appearing in the pivot
> > table?
> >
> > Thanks!
>
>
>

Tom,

Sure. You need to use the worksheet activate event, and a macro that will record what items are
shown, to re-show them after the pivot table is refreshed (also done through code).

Copy the first sub into the worksheet's codemodule: copy the code, right-click on the sheet tab (the
sheet with the pivot table), select "View Code", and then paste the code into the window that
appears.

Copy the second sub into a standard codemodule: you need to change the Pivot Table name and Pivot
Field name to reflect your actual values.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Activate()
ShowOnlyPreviousItems
End Sub

Sub ShowOnlyPreviousItems()
Dim myShow As String
Dim PItem As PivotItem

myShow = ""

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer ID")
For Each PItem In .PivotItems
If PItem.Visible Then
myShow = myShow & PItem.Name & " "
End If
Next PItem
End With

Application.ScreenUpdating = False
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer ID")
For Each PItem In .PivotItems
If InStr(1, myShow, PItem.Name) > 0 Then
PItem.Visible = True
Else
PItem.Visible = False
End If
Next PItem
End With
Application.ScreenUpdating = True

End Sub




"Tom" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the reposnse Bernie, but we have many spreadsheets deployed out to
> users already, and more could be created by them. I'm looking for a better
> way to do this without adding new columns that users do not want to see. Can
> this behavior be overridden somehow? Is there another way to do this?
>
> "Bernie Deitrick" wrote:
>
>> Tom,
>>
>> Use another column: Include, with values of Yes or No, and include it in your pivot table,
>> showing
>> only Yes.
>>
>> Then when new data is added, enter No into that column for the new accounts, and it won't affect
>> the
>> current pivot table.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Tom" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a pivot table that contains accounts and reports sales by account.
>> > Users selected the accounts they want to see and save their spreadsheet. When
>> > new accounts are added to the accounts list, users do not want to see the new
>> > accounts. They only want to see the accounts they selected from a previous
>> > session when they refresh the pivot table.
>> >
>> > Does anyone know how to prevent the new accounts from appearing in the pivot
>> > table?
>> >
>> > Thanks!
>>
>>
>>