Copy Range until lastRow and paste in another worksheet

Question

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.

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

score 3 · Answer 1 · edited May 23 '17 at 12:19

3

The file size is a results of using the entire columns. This link covers 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

edited May 23 '17 at 12:19

Community

1
1

answered Feb 17 '16 at 06:28

Jonathan

653
1
10
13

This is pasting just one row? The code had to modified since data is being copied from WB2.sheet1 to WB1.CR Details. – GavinJones Feb 17 '16 at 06:57
You're right there was an issue with it. I've corrected it. – Jonathan Feb 17 '16 at 13:53

score 0 · Accepted Answer · edited May 23 '17 at 11:48

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)

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.

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

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

Copy Range until lastRow and paste in another worksheet

2 Answers2

Linked