Was wondering if anyone had experienced this behavior with pastespecial and texttocolumns before. Is this a known issue or something else?

The user first selects and copies (manually) unformated text from a daily e-mail message. The user then switches to excel and starts the macro. The desired effect is the macro pastes the unformated text to A1 and then converts the range A1:A5 to columns. After that the rest of the code works with the data.

Code looks like:
Range("A1").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False
Range("A1:A5").Select
Selection.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, Space:=True

However, the actual behavior was not expected. When the macro is run the "first time" code works as coded -- however on subsquent runs the "pastespecial" cmd appears to remember that a texttocolumns had been required and preformats the text. The texttocolumns cmd then fails as there is no text to convert. (for example the user enters a weeks worth of data at one time - each day is a seperate copy/paste-macro action)

If the spreadsheet is closed and reopened the behavior repeats -- first run fine, rest fail.

Following code fixed the problem:
Range("A1").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False
' Stupid code - paste command not consistant - some time remembers how the text was converted
' and the TextToColumn fails -- this test fixes that problem - if already converted don't
' do it again
With ActiveSheet
If IsEmpty(testrange) Then
Range("A1:A5").Select
Selection.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, Space:=True
End If
End With

"testrange" will contain data if the data has been formated correctly.

Any thoughts?