Excel likes to help by remembering settings from the last data|text to columns
that you've used.

I think I'd run a dummy data|text to columns, delimited, but remove all the
checkmarks.

This will reset excel's "memory".

You could even do it in code:

Option Explicit
Sub testme()

Dim dummyCell As Range

With ActiveSheet
Set dummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
End With

dummyCell.Value = "asdf"

dummyCell.TextToColumns Destination:=dummyCell, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1) ', TrailingMinusNumbers:=True

dummyCell.ClearContents

'try to reset last used cell
Set dummyCell = ActiveSheet.UsedRange

End Sub



Tom Harvey wrote:

Hello,
I have a macro that includes a text to columns operation using space as the
delimiter. After running the macro, when I paste more data into an Excel
spreadsheet space is still being used as the delimiter until I shut down and
re-launch Excel. Is there a statement that I can add to my macro that
would reset the delimiter for pasted data to tab?

TIA,

Tom

--

Dave Peterson