尝试在单独的工作表中复制粘贴单元时,VBA运行时错误1004 [英] VBA Run-time error 1004 when trying to copy paste cells in a separate sheet
问题描述
提出错误在线:
AccDnn.Range(Cells(2,71),Cells(RangéeFinAcc - 1,87))。 b $ b
我的代码:
code> Private Sub SaveRedButton_Click()
Dim SaveRedMssg As String,SaveRedTitre As String,SaveRedButtons As Integer,SaveRedAns As Integer
DimRangéeFinRedAs Long,DrpRed As Worksheet
DimRangéeFinAccAs Long,AccDnn As Worksheet
Application.ScreenUpdating = False
设置DrpRed = ThisWorkbook.Worksheets(Drapeaux Rouges)
设置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 desdonnées
SaveRedMssg =Voulez-vous enregistrer lesdonnéesdu formulaire& vbNewLine& Drapeaux Rouges - Bobineuse?
SaveRedButtons = vbYesNo + vbQuestion + vbDefaultButton1 + vbApplicationModal
SaveRedAns = MsgBox(SaveRedMssg,SaveRedButtons,SaveRedTitre)
如果SaveRedAns = 6然后
AccDnn.Range(Cells复制
AccDnn.Cells(RangéeFinRed - 18,71).PasteSpecial(xlPasteValues)
DrpRed.Range(Cells(19,1),单元格(RangéeFinRed,16))。复制
AccDnn.Cells(2,75).PasteSpecial(xlPasteValues)
其他:SaveRedAns = 7
Application.ScreenUpdating = True
退出子
如果
Application.ScreenUpdating = True
End Sub
此代码的目的是将数据从一页上的输入页面传输到数据存储表,全部在同一个工作簿中。数据从上到下编译到数据表上。因此,代码必须读取要在数据存储表中添加多少行数据,然后移动数据存储表中的数据,为输入数据腾出空间。
更新本节:
如果SaveRedAns = 6然后
With AccDnn
.Range(.Cells(2,71),.Cells(RangéeFinAcc - 1,87))。复制
.Cells(RangéeFinRed - 18,71).PasteSpecial(xlPasteValues)
结束与
与DrpRed
.Range(.Cells(19,1),.Cells(RangéeFinRed,16))。复制
结束
AccDnn.Cells(2,75 ).PasteSpecial(xlPasteValues)
否则:SaveRedAns = 7
Application.ScreenUpdating = True
退出子
结束如果
或不使用与
语句:
如果SaveRedAns = 6然后
AccDnn.Range(AccDnn.Cells(2,71),AccDnn.Cells(RangéeFinAcc - 1,87))。 py
AccDnn.Cells(RangéeFinRed - 18,71).PasteSpecial(xlPasteValues)
DrpRed.Range(DrpRed.Cells(19,1),DrpRed.Cells(RangéeFinRed,16))。复制
AccDnn.Cells(2,75).PasteSpecial(xlPasteValues)
其他:SaveRedAns = 7
Application.ScreenUpdating = True
退出Sub
如果
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.
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运行时错误1004的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!