If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Pivot Table - Old remaining values for rows
Dear experts,
We are developing some reports with MS - Excel, using pivot tables. We created a pivot table, pointing to some information in the same XL document. But when we delete some data, it is still in the pivot filters data. We want a method of delete/refresh the data available for filter using MS-Excel commands or VBA-scripts. We don't want to recreate the pivot table. For instance, let's imagine that we have a database with the hospitals in the world. We create the following pivot table rows = cities, counting the hospitals in each city. PivotTable count of hospitals Madrid - 70 London - 85 Barcelona - 54 Paris - 82 John - 1 You can imagine that we have a wrong city (because of a test error) and John has appeared. We delete from the source data this row (we delete John or modify it to London), and refresh the pivot table. PivotTable count of hospitals Madrid - 70 London - 86 Barcelona - 54 Paris - 82 But if we try to select the cities, we can see the city John here!! We want to refresh this data in order to avoid John in the selection filter. Using VBA-Script or MS-Excel-PivotTable functionalities. Thanks in advance. HOW TO REPRODUCE THE ERROR: If you want to reproduce the issue, you can run the following script (Create a new workbook, create a module, paste the macro routine and execute it). You will see that there isn't data for City=John, but if you try to filter London, you will see that John is also available. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 15/04/2009 by zorion ' ' Sheets("Sheet3").Select ActiveCell.FormulaR1C1 = "City" Range("B1").Select ActiveCell.FormulaR1C1 = "NumHospitals" Range("A2").Select ActiveCell.FormulaR1C1 = "Barcelona" Range("B2").Select ActiveCell.FormulaR1C1 = "54" Range("A3").Select ActiveCell.FormulaR1C1 = "London" Range("B3").Select ActiveCell.FormulaR1C1 = "85" Range("A4").Select ActiveCell.FormulaR1C1 = "Madrid" Range("B4").Select ActiveCell.FormulaR1C1 = "70" Range("A5").Select ActiveCell.FormulaR1C1 = "Paris" Range("B5").Select ActiveCell.FormulaR1C1 = "82" Range("A6").Select ActiveCell.FormulaR1C1 = "John" Range("B6").Select ActiveCell.FormulaR1C1 = "1" Range("B7").Select Sheets("Sheet2").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet3!A:B").CreatePivotTable TableDestination:="Sheet2!R3C1", _ TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="City" With ActiveSheet.PivotTables("PivotTable1").PivotFields ("NumHospitals") .Orientation = xlDataField .Caption = "Sum of NumHospitals" .Function = xlSum End With Sheets("Sheet3").Select Range("A6").Select ActiveCell.FormulaR1C1 = "London" Range("B6").Select Sheets("Sheet2").Select Range("A7").Select ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub |
#2
|
|||
|
|||
Pivot Table - Old remaining values for rows
You could use a macro like the following:
Sub DelPivottableItem() Dim WhichFld As String, WhichItm As String WhichFld = InputBox("Which field has the item to be deleted?") If Len(WhichFld) = 0 Then Exit Sub WhichItm = InputBox("Which item should be deleted?") If Len(WhichItm) = 0 Then Exit Sub With ActiveSheet.PivotTables("PivotTable1").PivotFields (WhichFld) .PivotItems(WhichItm).Delete End With End Sub Hope this helps, Hutch "ZorionK" wrote: Dear experts, We are developing some reports with MS - Excel, using pivot tables. We created a pivot table, pointing to some information in the same XL document. But when we delete some data, it is still in the pivot filters data. We want a method of delete/refresh the data available for filter using MS-Excel commands or VBA-scripts. We don't want to recreate the pivot table. For instance, let's imagine that we have a database with the hospitals in the world. We create the following pivot table rows = cities, counting the hospitals in each city. PivotTable count of hospitals Madrid - 70 London - 85 Barcelona - 54 Paris - 82 John - 1 You can imagine that we have a wrong city (because of a test error) and John has appeared. We delete from the source data this row (we delete John or modify it to London), and refresh the pivot table. PivotTable count of hospitals Madrid - 70 London - 86 Barcelona - 54 Paris - 82 But if we try to select the cities, we can see the city John here!! We want to refresh this data in order to avoid John in the selection filter. Using VBA-Script or MS-Excel-PivotTable functionalities. Thanks in advance. HOW TO REPRODUCE THE ERROR: If you want to reproduce the issue, you can run the following script (Create a new workbook, create a module, paste the macro routine and execute it). You will see that there isn't data for City=John, but if you try to filter London, you will see that John is also available. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 15/04/2009 by zorion ' ' Sheets("Sheet3").Select ActiveCell.FormulaR1C1 = "City" Range("B1").Select ActiveCell.FormulaR1C1 = "NumHospitals" Range("A2").Select ActiveCell.FormulaR1C1 = "Barcelona" Range("B2").Select ActiveCell.FormulaR1C1 = "54" Range("A3").Select ActiveCell.FormulaR1C1 = "London" Range("B3").Select ActiveCell.FormulaR1C1 = "85" Range("A4").Select ActiveCell.FormulaR1C1 = "Madrid" Range("B4").Select ActiveCell.FormulaR1C1 = "70" Range("A5").Select ActiveCell.FormulaR1C1 = "Paris" Range("B5").Select ActiveCell.FormulaR1C1 = "82" Range("A6").Select ActiveCell.FormulaR1C1 = "John" Range("B6").Select ActiveCell.FormulaR1C1 = "1" Range("B7").Select Sheets("Sheet2").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet3!A:B").CreatePivotTable TableDestination:="Sheet2!R3C1", _ TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="City" With ActiveSheet.PivotTables("PivotTable1").PivotFields ("NumHospitals") .Orientation = xlDataField .Caption = "Sum of NumHospitals" .Function = xlSum End With Sheets("Sheet3").Select Range("A6").Select ActiveCell.FormulaR1C1 = "London" Range("B6").Select Sheets("Sheet2").Select Range("A7").Select ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub |
#3
|
|||
|
|||
Pivot Table - Old remaining values for rows
Here are a couple options:
Debra Dalgleish's website covers this topic: http://www.contextures.com/xlPivot04.html Also at that site is the PivotPlayPlus add-in that contains a handful of utilities for managing pivot tables and query tables: http://www.contextures.com/xlPivotPlayPLUS01.html Does that help? Regards, Ron Coderre Microsoft MVP (Excel) "ZorionK" wrote in message ... Dear experts, We are developing some reports with MS - Excel, using pivot tables. We created a pivot table, pointing to some information in the same XL document. But when we delete some data, it is still in the pivot filters data. We want a method of delete/refresh the data available for filter using MS-Excel commands or VBA-scripts. We don't want to recreate the pivot table. For instance, let's imagine that we have a database with the hospitals in the world. We create the following pivot table rows = cities, counting the hospitals in each city. PivotTable count of hospitals Madrid - 70 London - 85 Barcelona - 54 Paris - 82 John - 1 You can imagine that we have a wrong city (because of a test error) and John has appeared. We delete from the source data this row (we delete John or modify it to London), and refresh the pivot table. PivotTable count of hospitals Madrid - 70 London - 86 Barcelona - 54 Paris - 82 But if we try to select the cities, we can see the city John here!! We want to refresh this data in order to avoid John in the selection filter. Using VBA-Script or MS-Excel-PivotTable functionalities. Thanks in advance. HOW TO REPRODUCE THE ERROR: If you want to reproduce the issue, you can run the following script (Create a new workbook, create a module, paste the macro routine and execute it). You will see that there isn't data for City=John, but if you try to filter London, you will see that John is also available. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 15/04/2009 by zorion ' ' Sheets("Sheet3").Select ActiveCell.FormulaR1C1 = "City" Range("B1").Select ActiveCell.FormulaR1C1 = "NumHospitals" Range("A2").Select ActiveCell.FormulaR1C1 = "Barcelona" Range("B2").Select ActiveCell.FormulaR1C1 = "54" Range("A3").Select ActiveCell.FormulaR1C1 = "London" Range("B3").Select ActiveCell.FormulaR1C1 = "85" Range("A4").Select ActiveCell.FormulaR1C1 = "Madrid" Range("B4").Select ActiveCell.FormulaR1C1 = "70" Range("A5").Select ActiveCell.FormulaR1C1 = "Paris" Range("B5").Select ActiveCell.FormulaR1C1 = "82" Range("A6").Select ActiveCell.FormulaR1C1 = "John" Range("B6").Select ActiveCell.FormulaR1C1 = "1" Range("B7").Select Sheets("Sheet2").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet3!A:B").CreatePivotTable TableDestination:="Sheet2!R3C1", _ TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="City" With ActiveSheet.PivotTables("PivotTable1").PivotFields ("NumHospitals") .Orientation = xlDataField .Caption = "Sum of NumHospitals" .Function = xlSum End With Sheets("Sheet3").Select Range("A6").Select ActiveCell.FormulaR1C1 = "London" Range("B6").Select Sheets("Sheet2").Select Range("A7").Select ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub |
#4
|
|||
|
|||
Pivot Table - Old remaining values for rows
Thanks a lot!
The script found in your first link seems to fully solve our issue: Sub DeleteMissingItems2002All() 'prevents unused items in non-OLAP PivotTables Dim pt As PivotTable Dim ws As Worksheet Dim pc As PivotCache 'change the settings For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables pt.PivotCache.MissingItemsLimit = xlMissingItemsNone Next pt Next ws 'refresh all the pivot caches For Each pc In ActiveWorkbook.PivotCaches On Error Resume Next pc.Refresh Next pc End Sub Best regards Zorion "Ron Coderre" wrote: Here are a couple options: Debra Dalgleish's website covers this topic: http://www.contextures.com/xlPivot04.html Also at that site is the PivotPlayPlus add-in that contains a handful of utilities for managing pivot tables and query tables: http://www.contextures.com/xlPivotPlayPLUS01.html Does that help? Regards, Ron Coderre Microsoft MVP (Excel) "ZorionK" wrote in message ... Dear experts, We are developing some reports with MS - Excel, using pivot tables. We created a pivot table, pointing to some information in the same XL document. But when we delete some data, it is still in the pivot filters data. We want a method of delete/refresh the data available for filter using MS-Excel commands or VBA-scripts. We don't want to recreate the pivot table. For instance, let's imagine that we have a database with the hospitals in the world. We create the following pivot table rows = cities, counting the hospitals in each city. PivotTable count of hospitals Madrid - 70 London - 85 Barcelona - 54 Paris - 82 John - 1 You can imagine that we have a wrong city (because of a test error) and John has appeared. We delete from the source data this row (we delete John or modify it to London), and refresh the pivot table. PivotTable count of hospitals Madrid - 70 London - 86 Barcelona - 54 Paris - 82 But if we try to select the cities, we can see the city John here!! We want to refresh this data in order to avoid John in the selection filter. Using VBA-Script or MS-Excel-PivotTable functionalities. Thanks in advance. HOW TO REPRODUCE THE ERROR: If you want to reproduce the issue, you can run the following script (Create a new workbook, create a module, paste the macro routine and execute it). You will see that there isn't data for City=John, but if you try to filter London, you will see that John is also available. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 15/04/2009 by zorion ' ' Sheets("Sheet3").Select ActiveCell.FormulaR1C1 = "City" Range("B1").Select ActiveCell.FormulaR1C1 = "NumHospitals" Range("A2").Select ActiveCell.FormulaR1C1 = "Barcelona" Range("B2").Select ActiveCell.FormulaR1C1 = "54" Range("A3").Select ActiveCell.FormulaR1C1 = "London" Range("B3").Select ActiveCell.FormulaR1C1 = "85" Range("A4").Select ActiveCell.FormulaR1C1 = "Madrid" Range("B4").Select ActiveCell.FormulaR1C1 = "70" Range("A5").Select ActiveCell.FormulaR1C1 = "Paris" Range("B5").Select ActiveCell.FormulaR1C1 = "82" Range("A6").Select ActiveCell.FormulaR1C1 = "John" Range("B6").Select ActiveCell.FormulaR1C1 = "1" Range("B7").Select Sheets("Sheet2").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet3!A:B").CreatePivotTable TableDestination:="Sheet2!R3C1", _ TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="City" With ActiveSheet.PivotTables("PivotTable1").PivotFields ("NumHospitals") .Orientation = xlDataField .Caption = "Sum of NumHospitals" .Function = xlSum End With Sheets("Sheet3").Select Range("A6").Select ActiveCell.FormulaR1C1 = "London" Range("B6").Select Sheets("Sheet2").Select Range("A7").Select ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub |
Thread Tools | |
Display Modes | |
|
|