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

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

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