尝试在单独的工作表中复制粘贴单元格时出现 VBA 运行时错误 1004
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28324921/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Run-time error 1004 when trying to copy paste cells in a separate sheet
提问by BOB
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.
我目前在 VBA Excel 2007 代码中收到以下错误：运行时错误“1004”：对象“_Worksheet”的方法“范围”失败。通过标题中的这个错误解决了很多问题，我还没有找到类似的情况或解决我的问题的方法。也就是说，没有将我的变量声明为公共变量，我不想这样做，因为我在不同的子例程中多次使用相同的变量。
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.
此代码的目的是将数据从一张纸上的输入页面传输到数据存储表，所有这些都在同一个工作簿中。数据从上到下编译到数据表中。因此，代码必须读取应将多少行数据添加到数据存储表中，然后移动数据存储表中的数据为输入数据腾出空间。
回答by TheEngineer
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 Withstatements:
或者不使用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
