Hi,

As part of my code, I am performing a text to columns separation. During
this process, an Excel generated message comes up asking if I would like to
replace the contents of the cells in the columns where the data is being
separated to -- is there any way I can automate the clicking of "OK" on this
message box? Or not have it come up at all? Below is my code. Thanks!

With Sheets("Remove Dups")
.Columns("A:C").PasteSpecial
.Columns("B:B").Delete Shift:=xlToLeft
.Columns("B:B").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End With

--
Robert

you can try

application,displayalerts = false
With Sheets("Remove Dups")
.Columns("A:C").PasteSpecial
.Columns("B:B").Delete Shift:=xlToLeft
.Columns("B:B").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End With
application.displayalerts = true

--


Gary


"robs3131" <(E-Mail Removed)> wrote in message
news:CF384DF7-73D0-47E7-B59E-(E-Mail Removed)...
> Hi,
>
> As part of my code, I am performing a text to columns separation. During
> this process, an Excel generated message comes up asking if I would like to
> replace the contents of the cells in the columns where the data is being
> separated to -- is there any way I can automate the clicking of "OK" on this
> message box? Or not have it come up at all? Below is my code. Thanks!
>
> With Sheets("Remove Dups")
> .Columns("A:C").PasteSpecial
> .Columns("B:B").Delete Shift:=xlToLeft
> .Columns("B:B").TextToColumns Destination:=Range("A1"),
> DataType:=xlDelimited, _
> TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
> Tab:=True, _
> Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
> :="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
> End With
>
> --
> Robert

Typo in first line:
application,displayalerts = false
comma should be a period
application.displayalerts = false

Mike F
"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:eW$(E-Mail Removed)...
> you can try
>
> application,displayalerts = false
> With Sheets("Remove Dups")
> .Columns("A:C").PasteSpecial
> .Columns("B:B").Delete Shift:=xlToLeft
> .Columns("B:B").TextToColumns Destination:=Range("A1"),
> DataType:=xlDelimited, _
> TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
> Tab:=True, _
> Semicolon:=False, Comma:=False, Space:=False, Other:=True,
> OtherChar _
> :="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
> End With
> application.displayalerts = true
>
> --
>
>
> Gary
>
>
> "robs3131" <(E-Mail Removed)> wrote in message
> news:CF384DF7-73D0-47E7-B59E-(E-Mail Removed)...
>> Hi,
>>
>> As part of my code, I am performing a text to columns separation. During
>> this process, an Excel generated message comes up asking if I would like
>> to
>> replace the contents of the cells in the columns where the data is being
>> separated to -- is there any way I can automate the clicking of "OK" on
>> this
>> message box? Or not have it come up at all? Below is my code. Thanks!
>>
>> With Sheets("Remove Dups")
>> .Columns("A:C").PasteSpecial
>> .Columns("B:B").Delete Shift:=xlToLeft
>> .Columns("B:B").TextToColumns Destination:=Range("A1"),
>> DataType:=xlDelimited, _
>> TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
>> Tab:=True, _
>> Semicolon:=False, Comma:=False, Space:=False, Other:=True,
>> OtherChar _
>> :="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
>> End With
>>
>> --
>> Robert
>
>