title: VBA Run-time error 1004 when trying to copy paste cells in a separate sheet
body: <p>I am currently getting the following error in my VBA Excel 2007 code: Run-time error '1004': Method 'Range'of object '_Worksheet' failed. Having peeled through quite a few questions with this error in the title I haven't quite found a similar situation or a solution to my problem. That is, without declaring my variables as public, which, I don't want to do as I use the same variables multiple times in different subroutines.</p>
  
  <p>The error is raised on line:  </p>
  
  <pre><code>AccDnn.Range(Cells(2, 71), Cells(RangéeFinAcc - 1, 87)).Copy
  </code></pre>
  
  <p>My code:</p>
  
  <pre><code>Private Sub SaveRedButton_Click()
  
  Dim SaveRedMssg As String, SaveRedTitre As String, SaveRedButtons As Integer, SaveRedAns As Integer
  Dim RangéeFinRed As Long, DrpRed As Worksheet
  Dim RangéeFinAcc As Long, AccDnn As Worksheet
  
      Application.ScreenUpdating = False
  
      Set DrpRed = ThisWorkbook.Worksheets("Drapeaux Rouges")
      Set AccDnn = ThisWorkbook.Worksheets("Acc. données")
  
      RangéeFinRed = DrpRed.Cells(Rows.Count, 1).End(xlUp).Row
      RangéeFinAcc = AccDnn.Cells(Rows.Count, 75).End(xlUp).Row
      DrpRed.Cells(8, 2) = RangéeFinRed
      DrpRed.Cells(9, 2) = RangéeFinAcc
  
      SaveRedTitre = "Enregistrement des données"
      SaveRedMssg = "Voulez-vous enregistrer les données du formulaire" &amp; vbNewLine &amp; "«Drapeaux Rouges - Bobineuse»?"
      SaveRedButtons = vbYesNo + vbQuestion + vbDefaultButton1 + vbApplicationModal
      SaveRedAns = MsgBox(SaveRedMssg, SaveRedButtons, SaveRedTitre)
  
      If SaveRedAns = 6 Then
              AccDnn.Range(Cells(2, 71), Cells(RangéeFinAcc - 1, 87)).Copy
              AccDnn.Cells(RangéeFinRed - 18, 71).PasteSpecial (xlPasteValues)
              DrpRed.Range(Cells(19, 1), Cells(RangéeFinRed, 16)).Copy
              AccDnn.Cells(2, 75).PasteSpecial (xlPasteValues)
          Else: SaveRedAns = 7
              Application.ScreenUpdating = True
              Exit Sub
      End If
  
      Application.ScreenUpdating = True
  
  End Sub
  </code></pre>
  
  <p>The aim of this code is to transfer data form an input page on one sheet to a data storage sheet, all in the same workbook. The data is compiled onto the data sheet from the top down. Thus, the code must read how many rows of data shall be added to the data storage sheet and then move the data in the data storage sheet to make room for the input data.</p>