尝试在单独的工作表中复制粘贴单元格时出现 VBA 运行时错误 1004 [英] VBA Run-time error 1004 when trying to copy paste cells in a separate sheet

问题描述

我目前在我的 VBA Excel 2007 代码中遇到以下错误:运行时错误1004":对象_Worksheet"的方法范围"失败.在标题中出现了这个错误的很多问题之后，我还没有完全找到类似的情况或解决我的问题的方法.也就是说，没有将我的变量声明为公共的，我不想这样做，因为我在不同的子例程中多次使用相同的变量.

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.

在线引发错误:

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

我的代码:

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.

推荐答案

更新此部分:

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

或者不使用 With 语句:

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 运行时错误 1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！