Activesheet paste ошибка

Copy pasting 1 line of text from word to excel using VBA.

When the code reaches the below line I am getting the below error.

ActiveSheet.Paste

Run Time Error ‘1004’: Paste Method Of worksheet Class Failed error

But if I click Debug button and press F8 then it’s pasting the data in excel without any error.

This error occurs each time the loop goes on and pressing debug and F8 pasting the data nicely.

I did several testing and unable to find the root cause of this issue.

Also used DoEvents before pasting the data code but nothing worked.

Any suggestions?

EDIT:-

I am posting the code since both of you are saying the same. Here is the code for your review.

Sub FindAndReplace()
    Dim vFR As Variant, r As Range, i As Long, rSource As Range
    Dim sCurrRep() As String, sGlobalRep As Variant, y As Long, x As Long

    Dim NumCharsBefore As Long, NumCharsAfter As Long
    Dim StrFind As String, StrReplace As String, CountNoOfReplaces As Variant

    '------------------------------------------------
    Dim oWord As Object
    Const wdReplaceAll = 2

    Set oWord = CreateObject("Word.Application")
    '------------------------------------------------

    Application.ScreenUpdating = False

    vFR = ThisWorkbook.Sheets("Sheet1").Range("A1").CurrentRegion.Value

    On Error Resume Next
        Set rSource = Cells.SpecialCells(xlCellTypeConstants)
    On Error GoTo 0

    If Not rSource Is Nothing Then
        For Each r In rSource.Cells
            For i = 2 To UBound(vFR)
                If Trim(vFR(i, 1)) <> "" Then
                    With oWord
                        .Documents.Add
                            DoEvents
                            r.Copy
                            .ActiveDocument.Content.Paste

                            NumCharsBefore = .ActiveDocument.Characters.Count

                            With .ActiveDocument.Content.Find
                                .ClearFormatting
                                .Font.Bold = False
                                .Replacement.ClearFormatting
                                .Execute FindText:=vFR(i, 1), ReplaceWith:=vFR(i, 2), Format:=True, Replace:=wdReplaceAll
                            End With

                            .Selection.Paragraphs(1).Range.Select
                            .Selection.Copy
                            r.Select
                            ActiveSheet.Paste'Error occurs in this line pressing debug and F8 is pasting the data

                            StrFind = vFR(i, 1): StrReplace = vFR(i, 2)
                            NumCharsAfter = .ActiveDocument.Characters.Count
                            CountNoOfReplaces = (NumCharsBefore - NumCharsAfter) / (Len(StrFind) - Len(StrReplace))
                            .ActiveDocument.UndoClear
                        .ActiveDocument.Close SaveChanges:=False

                        If CountNoOfReplaces Then
                            x = x + 1
                            ReDim Preserve sCurrRep(1 To 3, 1 To x)
                            sCurrRep(1, x) = vFR(i, 1)
                            sCurrRep(2, x) = vFR(i, 2)
                            sCurrRep(3, x) = CountNoOfReplaces
                        End If
                        CountNoOfReplaces = 0
                    End With
                End If
            Next i
        Next r
    End If
   oWord.Quit
'Some more gode goes here... which is not needed since error occurs in the above loop
End Sub

If you want to know why I have chosen word for replacement then please go through the below link.
http://www.excelforum.com/excel-programming-vba-macros/1128898-vba-characters-function-fails-when-the-cell-content-exceeds-261-characters.html

Also used the code from the below link to get the number of replacements count.

http://word.mvps.org/faqs/macrosvba/GetNoOfReplacements.htm

OlegSmirnov Дата: Вторник, 27.02.2018, 05:52 |
Сообщение № 1

Здравствуйте.
Есть такой вопрос.

Макрос при вставке картинок на лист — выдает ошибку
«Run-time error ‘1004’ Метод Paste из класса Worksheet завершен неверно.»
И выделяет в коде слово «ActiveSheet.Paste».

Как это исправить ?

Код выглядит так:
[vba]

[/vba]

Сообщение отредактировал OlegSmirnov — Вторник, 27.02.2018, 05:53