Error while copying more than 8202 characters from one cell to another

Question

Problem - I have around more than 8202 characters in once cell say Range("A1").

Now I would like to copy the content of cell(A1) to cell(A2) using VBA. I'm using below Code

Sheets("XYZ").Range("A2") = Sheets("XYZ").Range("A1")

After the execution of the Code. It gives "Application Defined Or Object Defined Error !!"

Please help/assist with your expert comments.

Observation - If I reduce the length of "A1" cell to 8202 or less then about code works!

I'm Confused. Pls assist.

Answer 1

Change your code to

Sheets("XYZ").Range("A2") = Sheets("XYZ").Range("A1").Value

and it will work.

Not really sure why though, as .Value is the default property of a range.

Answer 2

I was able to duplicate your error with the following:

Sub Test8202Copy()
    Dim wks As Worksheet
    Set wks = Worksheets("Sheet1")
    Dim x As String

    For i = 0 To 8202
        x = x + "a"
    Next i

    wks.Range("A1").Value = x
    wks.Range("A2") = wks.Range("A1")
End Sub

---

I was able to solve the error by adding .value to the copy.

Sub Test8202Copy()
    Dim wks As Worksheet
    Set wks = Worksheets("Sheet1")
    Dim x As String

    For i = 0 To 8202
        x = x + "a"
    Next i

    wks.Range("A1").Value = x
    wks.Range("A2").Value = wks.Range("A1").Value
End Sub

---

Using an intermediate variable without the use of .Value seems to work:

Dim y As Variant
y = wks.Range("A1")
wks.Range("A2") = y

My guess so far is that 8202 exceed the character limit of the data type used when you don't define .Value. The in cell limit length is 32,767 (MS Excel 2010) which is almost 4x the 8201 value that clears.