vba 使用vba将未知范围复制并粘贴到excel中的不同工作表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17241557/
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
Copy and paste unknow range into a different worksheet in excel using vba
提问by Mike
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.
我正在尝试编写代码来复制未知数量的行并将其粘贴到单独工作表中的特定位置。到目前为止，我看到了下面的代码。我想从 A:F 列复制数据，行数未知，然后从 H6 开始粘贴。我在代码“Range("A1", lastrow).Select 中收到错误消息。错误是“对象工作表的方法范围失败”。感谢所有帮助。
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
回答by David Zemens
If you were to debug this, you would dicsover that the value of lastRowis -1. Get rid of the .Selectthere (and everywhere, for that matter). You also have an error in your range.Copywhich I fix:
如果你要调试这一点，你会dicsover该值lastRow是-1。摆脱.Select那里（以及任何地方，就此而言）。您的范围内也有错误.Copy，我已修复：
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
