vba 复制范围直到 lastRow 并粘贴到另一个工作表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35448682/
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 Range until lastRow and paste in another worksheet
提问by GavinJones
I'm presently using the below code to copy paste the range, but it creates a really large file once the data is pasted, how can I modify this to copy paste only till the last active row? Or is the file getting big because of some other factor? New to VB, any help would be much appreciated.
我目前正在使用下面的代码来复制粘贴范围，但是一旦粘贴数据，它会创建一个非常大的文件，我该如何修改它以仅复制粘贴到最后一个活动行？或者文件变大是因为其他一些因素？VB新手，任何帮助将不胜感激。
Private Sub CommandButton1_Click()
Dim WB1 As Workbook
Dim WB2 As Workbook
Set WB1 = ActiveWorkbook
Set WB2 = Workbooks.Open(WB1.Path & "\RawData.xlsm")
WB1.Sheets("CR Details").Columns("A:AW").Value = WB2.Sheets("sheet1").Columns("A:AW").Value
WB2.Close
End Sub
回答by Jonathan
The file size is a results of using the entire columns. This linkcovers some ways to find then end of a range of data. In your sub you can do this:
文件大小是使用整列的结果。此链接涵盖了一些方法来查找数据范围的结尾。在你的潜艇中，你可以这样做：
Private Sub CommandButton1_Click()
Dim WB1 As Workbook
Dim WB2 As Workbook
Set WB1 = ActiveWorkbook
Set WB2 = Workbooks.Open(WB1.Path & "\RawData.xlsm")
'Find the last row with data
With WB1.Sheets("CR Details")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
WB1.Sheets("CR Details").Range("A1:AW" & LastRow).Copy
WB2.Sheets("sheet1").Range("A1").PasteSpecial xlPasteValues
WB2.Close
End Sub
回答by Niklas Johansson
The short answer to your question is:
你的问题的简短回答是：
Private Sub CommandButton1_Click()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim LastRow As Long
Set WB1 = ActiveWorkbook
Set WB2 = Workbooks.Open(WB1.Path & "\RawData.xlsm")
With WB1.Sheets("CR Details")
'Find the last cell's row with data in any column
'(You can also use ".Cells(Rows.Count, 1).End(xlUp).row")
LastRow = .Range("A:AW").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
'Copy the values
WB2.Sheets("sheet1").Range("A1", "AW" & LastRow) = .Range("A1", "AW" & LastRow).Value
End With
WB2.Close
End Sub
A more detailed explanation:
更详细的解释：
A very common code snippet to find the last used row is to use:
查找最后使用的行的一个非常常见的代码片段是使用：
(will use column A to look for last used row)
（将使用 A 列查找最后使用的行）
Dim LastRow as Long
With ThisWorkbook.Sheets("CR Details")
LastRow = .Cells(Rows.Count, 1).End(xlUp).row
End With
Change "1" to the column number you want to look in.
将“1”更改为要查看的列号。
To find the row of the last used cell in any column in the range("A:AW"), then you would need something like this:
要在 中的任何列中找到最后使用的单元格的行range("A:AW")，那么您需要这样的东西：
Dim LastRow as Long
With ThisWorkbook.Sheets("CR Details").Range("A:AW")
LastRow = .Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
End With
That's it. Ron de Buinhas a great page explaining this. The answers can also be found on Stack Overflow.
就是这样。Ron de Buin有一个很好的页面解释了这一点。答案也可以在Stack Overflow上找到。
