Hi All,

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

Niall


--
NiallC
------------------------------------------------------------------------
NiallC's Profile: http://www.excelforum.com/member.php...o&userid=25948
View this thread: http://www.excelforum.com/showthread...hreadid=393246

I'm not sure how this may help, if you paste data, then Text to Columns
on that data, then paste some more, and more on another sheet, your new
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 all
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, any
delimiters.

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


NiallC Wrote:
Hi All,

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

Niall


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=393246

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 wrote:

Hi All,

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

Niall

--
NiallC
------------------------------------------------------------------------
NiallC's Profile: http://www.excelforum.com/member.php...o&userid=25948
View this thread: http://www.excelforum.com/showthread...hreadid=393246

--

Dave Peterson

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


--
NiallC
------------------------------------------------------------------------
NiallC's Profile: http://www.excelforum.com/member.php...o&userid=25948
View this thread: http://www.excelforum.com/showthread...hreadid=393246