setting delimiters for pasted data

[Tom Harvey]

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]

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]

Thanks for the suggestions Dave. I will give it a try. I was a little
surprised there isn't some simpler (fewer lines) code to do something like
this, but sometimes things are just not as simple as one imagines.

Tom