Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Criteria Missing Data
Hi,
I wonder if anyone has had similar problems.... I have created an automated Management Reporting Dashboard. Data is being imported from external sources, which are pivoted using static pivot tables and dynamic named ranges. In general it all works fine, except that from time to time the pivot criteria drop downs dont show the correct data to choose from. The code, when importing the data, cycles round all pivot tables and refreshes. Also when you clear the spreadsheet of old data the code turns of the saving of data within the Pivot table so its all clear. No matter what I do, the only way for the pivot to refresh itself properley and show the correct data within the drop downs is to delete and recreate the pivot. Any thoughts would be much appreciated. Chris. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Criteria Missing Data
Chris,
Try clearing and refreshing the pivot table cache. Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.MissingItemsLimit = xlMissingItemsNone Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.Refresh Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.MissingItemsLimit = xlMissingItemsDefault Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.Refresh Hope that works for you. MSweetG222 -- Thx MSweetG222 " wrote: Hi, I wonder if anyone has had similar problems.... I have created an automated Management Reporting Dashboard. Data is being imported from external sources, which are pivoted using static pivot tables and dynamic named ranges. In general it all works fine, except that from time to time the pivot criteria drop downs dont show the correct data to choose from. The code, when importing the data, cycles round all pivot tables and refreshes. Also when you clear the spreadsheet of old data the code turns of the saving of data within the Pivot table so its all clear. No matter what I do, the only way for the pivot to refresh itself properley and show the correct data within the drop downs is to delete and recreate the pivot. Any thoughts would be much appreciated. Chris. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Criteria Missing Data
On 27 Oct, 19:40, MSweetG222
wrote: Chris, Try clearing and refreshing the pivot table cache. Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.MissingItemsLimi*t = xlMissingItemsNone Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.Refresh Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.MissingItemsLimi*t = xlMissingItemsDefault Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.Refresh Hope that works for you. MSweetG222 -- Thx MSweetG222 " wrote: Hi, I wonder if anyone has had similar problems.... I have created an automated Management Reporting Dashboard. *Data is being imported from external sources, which are pivoted using static pivot tables and dynamic named ranges. In general it all works fine, except that from time to time the pivot criteria drop downs dont show the correct data to choose from. *The code, when importing the data, cycles round all pivot tables and refreshes. *Also when you clear the spreadsheet of old data the code turns of the saving of data within the Pivot table so its all clear. No matter what I do, the only way for the pivot to refresh itself properley and show the correct data within the drop downs is to delete and recreate the pivot. Any thoughts would be much appreciated. Chris.- Hide quoted text - - Show quoted text - Thanks MSweetG222, but no luck I'm afraid. This is similar to code I'm already using, although I hadnt considered the second cache refresh. Still, your code works and does the refresh but still, when I click on the drop down the data is incorrect, yet if I move that criteria drop down out of the pivot criteria area, refresh the pivot table, and put it back in everything is ok. Its driving me mad!!! I recorded the macro to do that automatically. It produced the following code.. ActiveSheet.PivotTables("EnqOrderPeriod").PivotFie lds("Exec Location").Orientation = xlHidden ActiveSheet.PivotTables("EnqOrderPeriod").PivotCac he.Refresh With ActiveSheet.PivotTables("EnqOrderPeriod").PivotFie lds("Exec Location") .Orientation = xlPageField .Position = 3 End With This, when run, fixes the problem .....but... I'd like to loop around all the 13 or so pivot tables in the workbook and run some code against them to make sure this problem is resolved automatically (as the end users are not going to be able to understand if there is a problem) and unless I manually specify every single pivot field within the Page Field (criteria) it is not going to work. Thanks for your help, its much appreciated. Chris. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Criteria Missing Data
Hi
This is a known problem in Excel versions below XL2007. Debra Dalgleish has code to clear the old items on her site http://www.contextures.com/xlPivot04.html -- Regards Roger Govier wrote in message ... On 27 Oct, 19:40, MSweetG222 wrote: Chris, Try clearing and refreshing the pivot table cache. Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.MissingItemsLimi*t = xlMissingItemsNone Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.Refresh Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.MissingItemsLimi*t = xlMissingItemsDefault Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.Refresh Hope that works for you. MSweetG222 -- Thx MSweetG222 " wrote: Hi, I wonder if anyone has had similar problems.... I have created an automated Management Reporting Dashboard. Data is being imported from external sources, which are pivoted using static pivot tables and dynamic named ranges. In general it all works fine, except that from time to time the pivot criteria drop downs dont show the correct data to choose from. The code, when importing the data, cycles round all pivot tables and refreshes. Also when you clear the spreadsheet of old data the code turns of the saving of data within the Pivot table so its all clear. No matter what I do, the only way for the pivot to refresh itself properley and show the correct data within the drop downs is to delete and recreate the pivot. Any thoughts would be much appreciated. Chris.- Hide quoted text - - Show quoted text - Thanks MSweetG222, but no luck I'm afraid. This is similar to code I'm already using, although I hadnt considered the second cache refresh. Still, your code works and does the refresh but still, when I click on the drop down the data is incorrect, yet if I move that criteria drop down out of the pivot criteria area, refresh the pivot table, and put it back in everything is ok. Its driving me mad!!! I recorded the macro to do that automatically. It produced the following code.. ActiveSheet.PivotTables("EnqOrderPeriod").PivotFie lds("Exec Location").Orientation = xlHidden ActiveSheet.PivotTables("EnqOrderPeriod").PivotCac he.Refresh With ActiveSheet.PivotTables("EnqOrderPeriod").PivotFie lds("Exec Location") .Orientation = xlPageField .Position = 3 End With This, when run, fixes the problem .....but... I'd like to loop around all the 13 or so pivot tables in the workbook and run some code against them to make sure this problem is resolved automatically (as the end users are not going to be able to understand if there is a problem) and unless I manually specify every single pivot field within the Page Field (criteria) it is not going to work. Thanks for your help, its much appreciated. Chris. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Criteria Missing Data
Chris,
Roger's right. Go to Contextures website and review the code at http://www.contextures.com/xlPivot04.html#Clear2002 Instead of using her code inside the loop, replace it with your code. In her code "pt" would equal "ActiveSheet.PivotTables("EnqOrderPeriod") in your code". That will get you your loop. I don't understand why the Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.MissingItemsLimiÂ*t= xlMissingItemsNone line of code didn't work. Hmmmm. -- Thx MSweetG222 "Roger Govier" wrote: Hi This is a known problem in Excel versions below XL2007. Debra Dalgleish has code to clear the old items on her site http://www.contextures.com/xlPivot04.html -- Regards Roger Govier wrote in message ... On 27 Oct, 19:40, MSweetG222 wrote: Chris, Try clearing and refreshing the pivot table cache. Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.MissingItemsLimiÂ*t = xlMissingItemsNone Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.Refresh Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.MissingItemsLimiÂ*t = xlMissingItemsDefault Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.Refresh Hope that works for you. MSweetG222 -- Thx MSweetG222 " wrote: Hi, I wonder if anyone has had similar problems.... I have created an automated Management Reporting Dashboard. Data is being imported from external sources, which are pivoted using static pivot tables and dynamic named ranges. In general it all works fine, except that from time to time the pivot criteria drop downs dont show the correct data to choose from. The code, when importing the data, cycles round all pivot tables and refreshes. Also when you clear the spreadsheet of old data the code turns of the saving of data within the Pivot table so its all clear. No matter what I do, the only way for the pivot to refresh itself properley and show the correct data within the drop downs is to delete and recreate the pivot. Any thoughts would be much appreciated. Chris.- Hide quoted text - - Show quoted text - Thanks MSweetG222, but no luck I'm afraid. This is similar to code I'm already using, although I hadnt considered the second cache refresh. Still, your code works and does the refresh but still, when I click on the drop down the data is incorrect, yet if I move that criteria drop down out of the pivot criteria area, refresh the pivot table, and put it back in everything is ok. Its driving me mad!!! I recorded the macro to do that automatically. It produced the following code.. ActiveSheet.PivotTables("EnqOrderPeriod").PivotFie lds("Exec Location").Orientation = xlHidden ActiveSheet.PivotTables("EnqOrderPeriod").PivotCac he.Refresh With ActiveSheet.PivotTables("EnqOrderPeriod").PivotFie lds("Exec Location") .Orientation = xlPageField .Position = 3 End With This, when run, fixes the problem .....but... I'd like to loop around all the 13 or so pivot tables in the workbook and run some code against them to make sure this problem is resolved automatically (as the end users are not going to be able to understand if there is a problem) and unless I manually specify every single pivot field within the Page Field (criteria) it is not going to work. Thanks for your help, its much appreciated. Chris. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data missing in field in pivot table | Excel Discussion (Misc queries) | |||
Pivot Table missing data item | Excel Worksheet Functions | |||
Excel 2007 (BETA) Missing data when doing pivot table | Excel Discussion (Misc queries) | |||
pivot table - two colums of data are missing | Excel Discussion (Misc queries) | |||
missing data on Pivot table | Excel Discussion (Misc queries) |