Copy cell with more than 921 charcters

[Solved/Closed]
Report
Posts
3
Registration date
Friday December 2, 2011
Status
Member
Last seen
December 5, 2011
-
Posts
3
Registration date
Friday December 2, 2011
Status
Member
Last seen
December 5, 2011
-
Hello,

I wrote a vb code to copy all rows where column B contains "RW". The code first deletes any data that the sheet might already have. Following is the code:

Sub Renewals_Click()
Set e = Sheets("Renewals")
Set k = Sheets("To be Engaged")

Dim d
Dim m
d = 4
m = 2

Do Until IsEmpty(e.Range("B" & d))

e.Rows(d).Value = ""
d = d + 1
Loop

d = 3
Do Until IsEmpty(k.Range("B" & m))

If k.Range("B" & m) = "RW" Then
d = d + 1
e.Rows(d).Value = k.Rows(m).Value
End If
m = m + 1
Loop
End Sub

I hit error 1004 when this code tries to copy a cell that contains more than 921 characters. Does anyone has a solution to overcome this error?!?!


Thanks in advance!

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I am not sure about the character limit. However how about trying to truely copying and paste instead of
e.Rows(d).Value = k.Rows(m).Value


e.Rows(d).copy
k.cells(m,1).pastespecial
Posts
3
Registration date
Friday December 2, 2011
Status
Member
Last seen
December 5, 2011

I am not sure how .copy works but when I replaced that code with your lines it left the entire sheet blank and didnt copy anything!

Ofcourse it didnt throw the error 1004 which I was earlier getting from my code. But my code crashed at the cell that had more than 921 characters!!

Thanks for your help again!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
oops
it was supposed to be

k.Rows(m).copy
e.cells(d,1).pastespecial

My bad..
Posts
3
Registration date
Friday December 2, 2011
Status
Member
Last seen
December 5, 2011

Super... that worked perfect!

Many thanks!! :)