Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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" wrote in message ... 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! |
#3
|
|||
|
|||
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" wrote in message ... 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! |
#4
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Data In Pivot Table | New Users to Excel | |||
How to view summarized items in a pivot table | New Users to Excel | |||
Pivot Table Problems | Excel Discussion (Misc queries) | |||
Can't group pivot table items by month in Excel | Excel Discussion (Misc queries) | |||
Pivot Table - Group by Month - Show Items with no data | Excel Discussion (Misc queries) |