VBA Run-time error 1004 when trying to copy paste cells in a separate sheet

Question

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.

The error is raised on line:

AccDnn.Range(Cells(2, 71), Cells(RangéeFinAcc - 1, 87)).Copy

My 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" & vbNewLine & "«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

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.

There's no obvious reason why publicly scoped variables would resolve this problem, so that is the wrong approach anyways. Which line raises the error -- AccDnn.Cells(RangéeFinRed - 18, 71).PasteSpecial (xlPasteValues)? If so, what is the Address of the RangeeFinRed range? — David Zemens
@DavidZemens Edited with offending code line in question pointed out. — BOB
AccDnn.Range(Cells(2, 71), Cells(RangéeFinAcc - 1, 87)).Copy attempts to specify a Range object which potentially spans multiple worksheets. Unqualified Cells always refers to the ActiveWorksheet. If this is not AccDnn then it will fail. — David Zemens

TheEngineer TheEngineer · Accepted Answer · 2015-02-04T15:26:45

Update this section:

If SaveRedAns = 6 Then
        With AccDnn
            .Range(.Cells(2, 71), .Cells(RangéeFinAcc - 1, 87)).Copy
            .Cells(RangéeFinRed - 18, 71).PasteSpecial (xlPasteValues)
        End With
        With DrpRed
            .Range(.Cells(19, 1), .Cells(RangéeFinRed, 16)).Copy
        End With
        AccDnn.Cells(2, 75).PasteSpecial (xlPasteValues)
    Else: SaveRedAns = 7
        Application.ScreenUpdating = True
        Exit Sub
End If

Or without using With statements:

If SaveRedAns = 6 Then
        AccDnn.Range(AccDnn.Cells(2, 71), AccDnn.Cells(RangéeFinAcc - 1, 87)).Copy
        AccDnn.Cells(RangéeFinRed - 18, 71).PasteSpecial (xlPasteValues)
        DrpRed.Range(DrpRed.Cells(19, 1), DrpRed.Cells(RangéeFinRed, 16)).Copy
        AccDnn.Cells(2, 75).PasteSpecial (xlPasteValues)
    Else: SaveRedAns = 7
        Application.ScreenUpdating = True
        Exit Sub
End If

VBA Run-time error 1004 when trying to copy paste cells in a separate sheet

1 Answers