This article describes this as a limitation for copying the entire sheet

http://support.microsoft.com/default...&Product=xlw2K
XL2000: "255 Characters in Cell" Error When Copying Worksheet

and the solution is to copy cells - so I haven't heard of this being a
problem if copying a range (something less than the worksheet itself).

I did this in Excel 2000

Sub TestCopy()
Debug.Print Len(ActiveCell)
ActiveCell.Copy Destination:=ActiveSheet.Next.Range("B9")
Debug.Print Len(ActiveSheet.Next.Range("B9"))
End Sub

it produced:
678
678

so It had no problem doing the copy

This
Sub TestCopy()
Debug.Print Len(ActiveCell)
ActiveCell.Copy
ActiveSheet.Next.Range("B9").PasteSpecial xlValues
Debug.Print Len(ActiveSheet.Next.Range("B9"))
End Sub

produced as well
678
678

--
Regards,
Tom Ogilvy

"Xavalon" wrote in message
om...
Hi All,

I hit the 255 char limit when coping cells (more than 255 chars are
truncated), but even after looking through tens of pages I couldn't
find any good answer. My hope it that someone here knows a good answer

BTW this is from my work computer, Excel 2000, but I had the same on a
Excel 2003 computer.

I have made a Excel VBA sub that merges 2 sheets in 2 books together
on a new sheet; However sheet A, containing some cells larger then 255
chars but has less rows than sheet B. Copying sheet A to C first is no
option.

my code simplified:

....
Dim c as Range
Dim i as Interger, rw as Integer
set wsa workbook("filenameA.xls").worksheets("A")
set wsb workbook("filenameB.xls").worksheets("B")
set wsc workbook("filenameB.xls").worksheets("C")
....
' I lookup certain values in sheet B and copying them to sheet C....
....
' value to be looked up in sheet A is in variable i now
......
' set range in where to find i
' rw contains correct row in worksheet c to write values
....
Set c = .Find(i, LookIn:=xlValues)
If Not c Is Nothing Then

' trying to overcome with copying an paste special
wsa.Cells(c.row, 8).Copy
wsc.Cells(rw, 10).PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' this apparently doesn't copy more then 255 chars

' found some code op MS website,
' to be honost I am not a regular VBA programmer, don't understand the
' Dim st2() as integer very well.
' Argh! is it not working too well, getting errors on cells with more
than
' 255 chars. Others are copied.

Dim st1 as string
Dim st2() as string
Dim x as integer

st1 = wsb.cells(c.row,2).value
For x = 1 To (Int(Len(st1) / 255) + 1)
st2(x) = Mid(st1, ((x - 1) * 255) + 1, 255)
Next x

wsc.cells(rw,11) = st2
' Argh! is it not working too well, getting errors on cells with more
than
' 255 chars. Others are copied.

end if

------------