ok nevermind I got it les, This code will only work with a userform in the
workbook.

Sub notabs()
Dim myd As dataobject
Set myd = New dataobject
Cells(1, 1).Copy
myd.getfromclipboard
mydat = myd.GetText(1)
mydat = Replace(mydat, Chr(9), "")
End Sub

that takes the value in A1 and removes formatting and tabs.
Ben
--
When you lose your mind, you free your life.


"ben" wrote:

There is a way to paste the buffer into a variable, from there you can use
the replace method to remove tabs eg....
myvar = replace(myvar,chr(9),"')
as to how to paste the buffer into a variable, i'm not quite sure how that
is done, anybody else know?
--
When you lose your mind, you free your life.


"Les Landau" wrote:

Hi,

I have a macro that pastes what is in the copy buffer, and then analyses the
resultant paste. Problem is that if the copy buffer has tabs in it, then
excel will interpret this as having to paste over a variety of columns. I
just want it to ignore any characters that may take the data over multiple
columns (e.g. like tabs).

The command I am using is:

ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

I thought the Format of Text would ignore the tabs.

Any help is most appreciated.

Cheers,
Les Landau