vba excel copy and paste limits 1

[Chrisg288]

hi.. I'm not a professional programmer, but do build programs to assist at work.

The following is part of a program I use in Excel to keep
meeting minutes. It is a macro to search through all rows,
and pull all open items of business, and append to a new
sheet. What is happening is the cut or paste is limiting
the number of characters to 255. (not a cell limit)
I am using excel 2003.

Is there a way to get around this. (snippet below)

'********************************************
Sub GET_OPEN_ITEMS()
Dim myRange As Range
Dim myString As String
Dim SrcRowCtr As Integer
Dim DestRowCtr As Integer
Dim myDate As String
Dim myReport As String
Dim DestRng As String
Dim myCell As String

DestRowCtr = 3
'2008
Sheets("2008").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2
For SrcRowCtr = 3 To 209
If Cells(SrcRowCtr, 10) = "OP" Then
myString = SrcRowCtr & ":" & SrcRowCtr
Range(myString).Select
Range(myString).EntireRow.Copy
DoEvents

Sheets("Report").Select
DoEvents
DestRng = "A" & CStr(DestRowCtr) & ":" & "J" & CStr(DestRowCtr)
Range(DestRng).Select
Range(DestRng).PasteSpecial xlPasteAll
DoEvents
DestRowCtr = DestRowCtr + 1
Sheets("2008").Select
End If
Next
ActiveSheet.Outline.ShowLevels RowLevels:=1
'********************************************

 

[Bong]

Why not just set the destination range equal to the source range (no copy/paste)? That is:

set rngSource = sheets("2008").range(myString)
set rngDest = sheets("Report").range(DestRng)
rngDest.value = rngSource.value

_________________
Bob Rashkin

 

[Chrisg288]

Thank you Bob for replying.

I can't see that it worked, as it still seems to have clipped the longer cells.

I am attempting to attach the file.. compare item 49-12-07 after you run the report, and you will see that it is different from the original cell.

thanks

 

[Chrisg288]

trying again.. I have corrected the filename I think.

 

[Bong]

I couldn't open the file but I did a little experiment that should be pertinent:

[a1]=string(280,"a")
[b1]=string(280,"b")
[c1]=string(280,"c")

Now "a1" has 280 "a"s, "b1" has 280 "b"s, "c1" has 280 "c"s.

sheet1.Range(cells(4,3),cells(4,5))=sheet1.range(cells(1,1),cells(1,3)).Value

Now "c4" has 280 "a"s, etc. There does not appear to be any clipping.

_________________
Bob Rashkin

 

[Gavona]

Are you sure that the cells have been truncated? Sometimes the wordwrapping can fail so it just looks that way. Also the row height may need adjusting manually.

Gavin

 

[Gavona]

http://ewbi.blogs.com/develops/2005/01/excel_s_text.html

Gavin