使用VBA将unknow范围复制并粘贴到excel中的其他工作表中 [英] Copy and paste unknow range into a different worksheet in excel using vba

问题描述

我正在尝试编写代码以复制未知数量的行并将其粘贴到单独工作表中的特定位置。到目前为止，我已经看到了下面的代码。我想复制A：F列中的数据（行数未知），然后将其粘贴到H6中。我在代码 Range（ A1，lastrow）.Select中收到错误。错误是对象工作表的方法范围失败。感谢所有帮助。

I am trying to write a code to copy an unknown number of rows and paste it into a specific location in a separate worksheet. So far I have the code seen below.I Want to copy the data from columns A:F, for an unknown number of rows, and paste it starting in H6. I get an error in the code "Range("A1", lastrow).Select". The error is "Method range of object worksheet failed". All help is appreciated.

Dim lastrow As Long
Dim copyrange As Range

lastrow = Range("A65536").End(xlUp).Select
 Range("A1", lastrow).Select
    Selection.Copy
    Sheets("Final").Select
    Range("H6").Select
    ActiveSheet.Paste
End Sub

推荐答案

如果要对此进行调试，您将发现 lastRow 的值为 -1 。摆脱。在那里选择（就此而言，在任何地方）。您在范围中也有一个错误。复制我已修复：

If you were to debug this, you would dicsover that the value of lastRow is -1. Get rid of the .Select there (and everywhere, for that matter). You also have an error in your range.Copy which I fix:

Sub Test()
Dim lastrow As Long

   lastrow = Range("A65536").End(xlUp).Row
   Range("A1:F" & lastrow).Copy Destination:=Sheets("Final").Range("H6")

End Sub

或者，只是传输值，我认为这样做（未测试）：

Or, to just transfer the values, I think this will do it (untested):

Sub Test2()
Dim copyRange as Range
   Set copyRange = Range("A1:F" & Range("A65536").End(xlUp).Row)
   With copyRange
       Sheets("Final").Range("H6").Resize(.Rows.Count, .Columns.Count).Value = .Value
   End With
End Sub

这篇关于使用VBA将unknow范围复制并粘贴到excel中的其他工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！