Replaceing destination cell contents with new data in Excell

[Duane L Kibby]

Hi
I am using the following code to replace old data with new.

Set objRange1 =
Application.Workbooks("Analysis.xls").Worksheets("Data").Range("B2").EntireColumn
Set ObjRange2 =
Application.Workbooks("Analysis.xls").Worksheets("Data").Range("C2").EntireColumn

objRange1.TextToColumns ObjRange2, , , , , , True

The problem is, unless I do a manual delete on "ObjRange2" then I get the
prompt,

"Do you want to replace the contents of the destination cells?"

I always want to replace and to do programatically. I am using Excel 2000
and Windows 2000.

Thanks, Duane

 

[PSKelligan]

Try

OnError GoTo codeBreak
SetWarnings No

'before your code and at the end of your code put

codeBreak:
SetWarnings Yes

This can be dangerous so be certain to add the second part. The Goto

 

[Jim Thomlinson]

Use
application.displayalerts = false
and
application.displayalerts = true

To change whether the prompt is shown. As this is an application level
setting you should be sure to use an error handler to reset the value in case
of a crash.

HTH

 

[PSKelligan]

Disregard previous...whron app.

Application.DisplayAlerts = False

Application.DisplayAlerts = True

!!Be sure to turn the Alerts back on at the end of you proceedure!!