英文:
Pasting range in next empty cell causes run-time error 424: 'Object Required'
问题
这个错误是由于以下 "Paste" 宏的这一部分触发的:
Worksheets("Sheet2").Range("A" & lr2).Offset(1, 0).PasteSpecial.xlValues
具体来说,这个错误是因为 .PasteSpecial.xlValues
的一部分触发的,它试图将复制的内容以 "Values" 的形式粘贴到目标工作表中的下一个空单元格,但是似乎没有正确设置对象,因此触发了 "Object Required" 的运行时错误 424。
英文:
I copied a range from one worksheet into the next empty row in another worksheet through below code. But this raise an exception
>run-time error 424; 'Object Required'.
Sub copy()
Dim lr1 As Long
lr1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet1").Range("A1:A" & lr1).copy
End Sub
Sub paste()
Dim lr2 As Long
lr2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet2").Range("A" & lr2).Offset(1, 0).PasteSpecial.xlValues
End Sub
The error occurs when I run the "Paste" macro. I want to reiterate that the copied range does get pasted in the next empty cell, however this error message get displayed.
Which part of the "Paste" macro triggers this message?
答案1
得分: 1
当粘贴数值时,请跳过剪贴板:
Sub copy()
Dim lr1 As Long
lr1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("A1:A" & lr1)
Dim lr2 As Long
lr2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet2").Range("A" & lr2).Offset(1, 0).Resize(rng.Rows.Count).Value = rng.Value
End Sub
英文:
When pasting just values, skip the clipboard:
Sub copy()
Dim lr1 As Long
lr1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("A1:A" & lr1)
Dim lr2 As Long
lr2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet2").Range("A" & lr2).Offset(1, 0).Resize(rng.Rows.Count).Value = rng.Value
End Sub
答案2
得分: 0
一个子程序和修改:
Sub copypaste()
Dim lr1 As Long, lr2 As Long
lr1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet1").Range("A1:A" & lr1).copy
lr2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet2").Range("A" & lr2).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End Sub
<details>
<summary>英文:</summary>
A single sub and modification:
Sub copypaste()
Dim lr1 As Long, lr2 As Long
lr1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet1").Range("A1:A" & lr1).copy
lr2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet2").Range("A" & lr2).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End Sub
</details>
# 答案3
**得分**: 0
我已按照BigBen的更正修改了我的代码如下;
```vba
Sub copy()
Dim lr1 As Long
Dim lr2 As Long
lr1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
lr2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet1").Range("A1:A" & lr1).copy
Worksheets("Sheet2").Range("A" & lr2).Offset(1, 0).PasteSpecial paste:=xlPasteValues
End Sub
@Scott Craner: 我有两个子程序,因为我是VBA的新手,这是一种将代码分解以便我能理解代码应该做什么的方法,但我想这可以通过注释来完成。
英文:
I've changed my code as per BigBen's correction to the following;
Sub copy()
Dim lr1 As Long
Dim lr2 As Long
lr1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
lr2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet1").Range("A1:A" & lr1).copy
Worksheets("Sheet2").Range("A" & lr2).Offset(1, 0).PasteSpecial paste:=xlPasteValues
End Sub
@Scott Craner: I had two subs as I'm new to vba and it's a way to break down the code so that I can understand what the code is supposed to do, but I guess that can be done with comments.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论