在下一个空单元格粘贴范围会导致运行时错误 424:’需要对象’

huangapple go评论90阅读模式
英文:

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(&quot;Sheet1&quot;).Range(&quot;A&quot; &amp; Rows.Count).End(xlUp).Row
lr2 = Worksheets(&quot;Sheet2&quot;).Range(&quot;A&quot; &amp; Rows.Count).End(xlUp).Row
Worksheets(&quot;Sheet1&quot;).Range(&quot;A1:A&quot; &amp; lr1).copy
Worksheets(&quot;Sheet2&quot;).Range(&quot;A&quot; &amp; 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.

huangapple
  • 本文由 发表于 2020年1月4日 01:06:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/59582535.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定