Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 117
Default Replace Contents of Destination Cells

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Replace Contents of Destination Cells

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 117
Default Replace Contents of Destination Cells

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"Do you want to replace the contents of the destination cells?" Bob Barnes Excel Discussion (Misc queries) 8 April 2nd 23 08:05 PM
Do you want to replace the destination cells? Martin ©¿©¬ @nohere.net Excel Discussion (Misc queries) 5 November 20th 09 05:43 AM
Excel VBA Paste Special - replace contents of destination cells Sam via OfficeKB.com Excel Programming 2 April 17th 06 12:24 AM
Macro causes "Do you want to replace the contents of the destination cells" JB[_2_] Excel Programming 1 October 16th 04 11:25 PM
Replace destination cells SmilingPolitely Excel Programming 3 April 18th 04 08:06 AM


All times are GMT +1. The time now is 12:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"