Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi all,
I'm using the following code to update data for a pivot table. Sometimes i get the following message: "Do you want to replace the contents of the destination cells in [worksheet name]?" I'm using the same code in another workbook, however, i never get that message. What makes this message appear in one workbook and not the other? The only difference is that the workbook that displays the message contains only 1 pivot table, while the other workbook contains 2 pivot tables. Could this be the reason why? I can't see how. Your help will be greatly appreciated. I'm using Excel 2003 The code is: Sub Refresh() ' Unprotect the sheet ActiveSheet.Unprotect ' Clear old items from the list and Refresh data Dim pt As PivotTable For Each pt In ActiveSheet.PivotTables pt.PivotCache.MissingItemsLimit = xlMissingItemsNone pt.PivotCache.Refresh Next pt Range("E2").Select ' protect sheet ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True _ , AllowFiltering:=True End Sub |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Untested...
Try adding: Application.displayalerts = false 'the line that causes the warning application.displayalerts = true ".displayalerts = false" can stop lots of warnings, but not all. Tendresse wrote: Hi all, I'm using the following code to update data for a pivot table. Sometimes i get the following message: "Do you want to replace the contents of the destination cells in [worksheet name]?" I'm using the same code in another workbook, however, i never get that message. What makes this message appear in one workbook and not the other? The only difference is that the workbook that displays the message contains only 1 pivot table, while the other workbook contains 2 pivot tables. Could this be the reason why? I can't see how. Your help will be greatly appreciated. I'm using Excel 2003 The code is: Sub Refresh() ' Unprotect the sheet ActiveSheet.Unprotect ' Clear old items from the list and Refresh data Dim pt As PivotTable For Each pt In ActiveSheet.PivotTables pt.PivotCache.MissingItemsLimit = xlMissingItemsNone pt.PivotCache.Refresh Next pt Range("E2").Select ' protect sheet ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True _ , AllowFiltering:=True End Sub -- Dave Peterson |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
It worked, thanks Dave.
"Dave Peterson" wrote: Untested... Try adding: Application.displayalerts = false 'the line that causes the warning application.displayalerts = true ".displayalerts = false" can stop lots of warnings, but not all. Tendresse wrote: Hi all, I'm using the following code to update data for a pivot table. Sometimes i get the following message: "Do you want to replace the contents of the destination cells in [worksheet name]?" I'm using the same code in another workbook, however, i never get that message. What makes this message appear in one workbook and not the other? The only difference is that the workbook that displays the message contains only 1 pivot table, while the other workbook contains 2 pivot tables. Could this be the reason why? I can't see how. Your help will be greatly appreciated. I'm using Excel 2003 The code is: Sub Refresh() ' Unprotect the sheet ActiveSheet.Unprotect ' Clear old items from the list and Refresh data Dim pt As PivotTable For Each pt In ActiveSheet.PivotTables pt.PivotCache.MissingItemsLimit = xlMissingItemsNone pt.PivotCache.Refresh Next pt Range("E2").Select ' protect sheet ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True _ , AllowFiltering:=True End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
"Do you want to replace the contents of the destination cells?" | Excel Discussion (Misc queries) | |||
Do you want to replace the destination cells? | Excel Discussion (Misc queries) | |||
Excel VBA Paste Special - replace contents of destination cells | Excel Programming | |||
Macro causes "Do you want to replace the contents of the destination cells" | Excel Programming | |||
Replace destination cells | Excel Programming |