title: Run Time Error '1004': Paste Method Of worksheet Class Failed error
body: <p>Copy pasting 1 line of text from word to excel using VBA.</p>
  
  <p>When the code reaches the below line I am getting the below error.</p>
  
  <pre><code>ActiveSheet.Paste
  </code></pre>
  
  <p><strong>Run Time Error '1004': Paste Method Of worksheet Class Failed error</strong></p>
  
  <p>But <strong>if I click Debug button and press F8 then it's pasting the data</strong> in excel without any error.</p>
  
  <p>This error occurs each time the loop goes on and pressing debug and F8 pasting the data nicely.</p>
  
  <p>I did several testing and unable to find the root cause of this issue.</p>
  
  <p>Also used <strong>DoEvents</strong> before pasting the data code but nothing worked.</p>
  
  <p>Any suggestions?</p>
  
  <p><strong>EDIT:-</strong></p>
  
  <p>I am posting the code since both of you are saying the same.  Here is the code for your review.</p>
  
  <pre><code>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)) &lt;&gt; "" 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
  </code></pre>
  
  <p>If you want to know why I have chosen word for replacement then please go through the below link.
  <a href="http://www.excelforum.com/excel-programming-vba-macros/1128898-vba-characters-function-fails-when-the-cell-content-exceeds-261-characters.html">http://www.excelforum.com/excel-programming-vba-macros/1128898-vba-characters-function-fails-when-the-cell-content-exceeds-261-characters.html</a></p>
  
  <p>Also used the code from the below link to get the number of replacements count.</p>
  
  <p><a href="http://word.mvps.org/faqs/macrosvba/GetNoOfReplacements.htm">http://word.mvps.org/faqs/macrosvba/GetNoOfReplacements.htm</a></p>