复制范围直到lastRow并粘贴到另一个工作表中 [英] Copy Range until lastRow and paste in another worksheet

问题描述

我正在使用下面的代码来复制粘贴范围，但是一旦粘贴数据，它会创建一个非常大的文件，那么我该怎么修改这个文件才能复制粘贴到最后一个活动的行？还是由于其他一些因素而使文件变大？
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

推荐答案

您的问题的简短答案是：

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 very common code snippet to find the last used row is to use:

（将使用列A查找最后使用的行）

(will use column A to look for last used row)

Dim LastRow as Long

With ThisWorkbook.Sheets("CR Details")
    LastRow = .Cells(Rows.Count, 1).End(xlUp).row 
End With

将1更改为列要查找

要查找范围（A：AW）中任何列中最后一个单元格的行，那么你需要这样的东西：

To find the row of the last used cell in any column in the range("A:AW"), then you would need something like this:

Dim LastRow as Long

With ThisWorkbook.Sheets("CR Details").Range("A:AW")
    LastRow = .Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
End With

就是这样。 Ron de Buin 有一个很好的页面解释这一点。答案也可以在堆栈溢出。

That's it. Ron de Buin has a great page explaining this. The answers can also be found on Stack Overflow.

这篇关于复制范围直到lastRow并粘贴到另一个工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！