title: What could cause Range.Copy to throw an error?
body: <p>I have the following line in my VBA code:</p>
  
  <pre><code>wks.Rows(lLastRow).Copy
  </code></pre>
  
  <p>Usually it works fine. lLastRow is always 11. wks is always the active sheet.</p>
  
  <p>But sometimes the line above starts throwing errors of the type: "copy method of range class failed".</p>
  
  <p>Then I have to restart the Excel file for the line to start working again.</p>
  
  <p>Do you have any idea what might be causing the problem? What could cause a <code>Range.Copy</code> statement to stop working and then start working again once the file is restarted?</p>
  
  <p><strong>EDIT</strong>: Here is more of my code. The code is started by a button calling <code>NewActionButton()</code></p>
  
  <pre><code>Sub NewActionButton()
      'Dim runState As New cRunState
      'Set runState = New cRunState
      If bProduction = True Then On Error GoTo ErrHandler
  
      'START
      Dim lStart As Long
      Dim lId As Long
      Dim lDate As Long
      Dim lEnd As Long
      Dim wks As Worksheet
  
      Set wks = GetSheetByCodename(ThisWorkbook, "wActions")
      lStart = 2
      lId = 3
      lDate = 2
      lEnd = 12
  
      Call NewRow(wks, lStart, lId, lDate, lEnd)
      'END
  
      On Error GoTo 0
  Exit Sub
  ErrHandler:
      MsgBox ("Ett of" &amp; oe &amp; "rutsett fel har skett.")
  End Sub
  </code></pre>
  
  <p>The procedure in which I get an error. the first time it fails it seems to throw an error on the .Insert statement. Then if I run the macro again it throws an error on the .Copy statement. If I restart the document, it works.</p>
  
  <pre><code>Sub NewRow(wks As Worksheet, lStartCol As Long, lIdCol As Long, lDate As Long, lEndCol As Long)
      Dim lLastRow As Long
      Dim rConstants As Range
      Dim rConstant As Range
      Dim rEarlierIds As Range
  
      'Determine last row based on criteria column
      lLastRow = LastRow(wks.Columns(lIdCol))
  
      'Copy template row from directly above
      wks.Range(wks.Cells(lLastRow, lStartCol), wks.Cells(lLastRow, lEndCol)).Copy
  
      'Insert new row
      wks.Range(wks.Cells(lLastRow + 1, lStartCol), wks.Cells(lLastRow + 1, lEndCol)).Insert
  
      'Remove unwanted properties
      Set rConstants = wks.Range(wks.Cells(lLastRow + 1, lStartCol), wks.Cells(lLastRow + 1, lEndCol)).SpecialCells(xlCellTypeConstants)
      If Not rConstants Is Nothing Then
          For Each rConstant In rConstants.Cells
              rConstant = ""
          Next rConstant
      End If
  
      'Add ID
      Set rEarlierIds = wks.Columns(lIdCol)
      wks.Cells(lLastRow + 1, lIdCol) = Evaluate("Max(" &amp; rEarlierIds.Address &amp; ")+1")
  
      'Add Date
      wks.Cells(lLastRow + 1, lDate) = Date
  
      'Remove copy marking
      Application.CutCopyMode = False
  End Sub
  </code></pre>
  
  <p><strong>EDIT2</strong>: The .Insert statement does insert a new range. Yet, despite this, execution stops and I get the error message "Insert method of Range class failed. So the line does it jobs, yet I get an error and execution stops.</p>
  
  <p><strong>EDIT3</strong>: I don't know what is wrong, but I have discovered a pattern. The .Insert statement works if I execute the macro immedietly after opening the Excel file. And I can execute it any number of times. But as soon as I double click on a cell and then click ESC to leave the cell, and then try running the macro again, then the .Insert statement throws an error. This makes no sense to me.</p>
  
  <p><strong>EDIT4</strong>:I have update the code above to how it currently looks.</p>
  
  <p><strong>EDIT5</strong>:The code below works flawlessly. What I have changed is that I don't copy and then insert. Instead I first insert a new row and then copy to it. I still don't know why my previous code didn't work. I hope someone has an idea or can confirm that I stumbled upon a bug and can say something about what circumstances cause the bug.</p>
  
  <pre><code>Sub NewRow(wks As Worksheet, lStartCol As Long, lIdCol As Long, lDate As Long, lEndCol As Long)
      Dim lLastRow As Long
      Dim rConstants As Range
      Dim rConstant As Range
      Dim rEarlierIds As Range
  
      'Determine last row based on criteria column
      lLastRow = LastRow(wks.Columns(lIdCol))
  
      'Insert new row
      wks.Range(wks.Cells(lLastRow + 1, lStartCol), wks.Cells(lLastRow + 1, lEndCol)).Insert
  
      'Copy template row from directly above
      wks.Range(wks.Cells(lLastRow, lStartCol), wks.Cells(lLastRow, lEndCol)).Copy
  
      'Paste formulas row
      wks.Range(wks.Cells(lLastRow + 1, lStartCol), wks.Cells(lLastRow + 1, lEndCol)).PasteSpecial xlPasteFormulas
  
      'Remove values
      Set rConstants = wks.Range(wks.Cells(lLastRow + 1, lStartCol), wks.Cells(lLastRow + 1, lEndCol)).SpecialCells(xlCellTypeConstants)
      If Not rConstants Is Nothing Then
          For Each rConstant In rConstants.Cells
              rConstant = ""
          Next rConstant
      End If
  
      'Add ID
      Set rEarlierIds = wks.Columns(lIdCol)
      wks.Cells(lLastRow + 1, lIdCol) = Evaluate("Max(" &amp; rEarlierIds.Address &amp; ")+1")
  
      'Add Date
      wks.Cells(lLastRow + 1, lDate) = Date
  
      'Remove copy marking
      Application.CutCopyMode = False
  End Sub
  </code></pre>