Pasting data without the worksheet automatically delimiting it?

[NiallC]

Hi All,

Quick question about Text to Columns function/pasting data. Basicall
i've created a Macro which involves using Text to Columns to delimi
some data. The macro is associated with a command button and is workin
fine.
The problem is that when I close and reopen the worksheet with th
command button in it and paste my data in, it automatically delimits i
before I run the macro. This renders the macro useless as its dependan
on the data being pasted in, in a certain format.
The only way I can get the sheet to accept the pasted data as norma
(not delimit it automatically) is to close down excel completely an
then reopen my worksheet. Is there a way to 'reset' the workshee
before I paste the data in, or some other way around this?
Any help would be greatly appreciated!!

Nial

 

[Bryan Hessey]

I'm not sure how this may help, if you paste data, then Text to Column
on that data, then paste some more, and more on another sheet, your ne
pastes go at the format that your last Text to Columns was set for.

If you then format (any other odd couple of cells) and remove al
delimiters you can then paste data that is no longer formatted, ie
retains it's Notepad format into column A etc.

Perhaps the end of your macro could 'unset' or set to nothing, an
delimiters.

Otherwise a manual Text to Columns of a couple of cells to manuall
unset the format that was retained might be needed.

 

[Dave Peterson]

If you do a dummy data|text to columns (delimited by nothing), then this'll
reset the settings.

You could add something like this to the bottom of your macro that does the
data|text to columns:

Dim DummyCell As Range
With ActiveSheet
Set DummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
With DummyCell
.Value = "asdf"
.TextToColumns Destination:=.Cells, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(1, 1)
.ClearContents
End With
End With

 

[NiallC]

Bryan, Dave,
Thanks a lot for your help on this, much appreciated.....
Niall