Copy a Range From One Worksheet To The Last Row of Data On Another

Question

I want to copy a range of cells in column "B" and paste it after the last row of data on an another sheet in the same workbook in the same column. I have the following code but I can't figure out how to actually paste the data.

Dim lastRow As String

Sheet9.Range("B2:B100").Select
Sheet9.Range(Selection,Selection.End(xlDown)).Select
Selection.Copy


lastRow = Sheet14.Cells(Rows.Count, "B").End(xlUp).Row + 1
Sheet14.Range("B" & lastRow).Activate
Sheet14.Paste

Do you want copying form B2 to last cell in B:B column? From B2 to the first empty cell? Anyhow, no need of any selection... — FaneDuru, Aug 22 '20 at 19:50

score 0 · Answer 1 · answered Aug 22 '20 at 20:04

0

Try the next code line, please:

Sheet9.Range("B2:B" & Sheet9.Range("B100").End(xlDown).Row).Copy _
    Destination:=Sheet14.Range("B" & Sheet14.Range("B" & Rows.count).End(xlUp).Row + 1)

It copies form B2 to the last empty row, after B100...

answered Aug 22 '20 at 20:04

FaneDuru

38,298
4
19
27

score 0 · Answer 2 · answered Aug 23 '20 at 06:11

Copy Range

To learn why the 1st code is not recommended, read through the following 'classic': How to avoid using Select in Excel VBA.
The 2nd code shows how to copy a range in three steps: define CopyRange, define PasteCell(PasteRange) and finally Copy/Paste.
To better understand the 2nd code, check out Range.Copy and Range.PasteSpecial from Microsoft, this tutorial or these videos.
To make the codes work in your case, replace Sheet1 with Sheet9, Sheet2 with Sheet14 and B10 with B100.

The Code

Option Explicit

Sub CopyPasteSelectActivate()
    
    ' A quick fix, not recommended. It's a poorly improved version of what
    ' the macro recorder what produce.
    ' Rather use one of the 3 ways below.
    
    Dim LastRow As Long ' In this case 'As String' works, too.
    
    Sheet1.Activate
    Sheet1.Range("B2:B10").Select
    Sheet1.Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    LastRow = Sheet2.Cells(Rows.Count, "B").End(xlUp).Row + 1
    Sheet2.Activate
    Sheet2.Range("B" & LastRow).Activate
    Sheet2.Paste

End Sub

Sub CopyPaste3Ways()
    
' Define CopyRange.
    
    Dim CopyRange As Range
    
    ' Either...
    'Set CopyRange = Sheet1.Range("B2", Sheet1.Range("B2:B10").End(xlDown))
    ' ...or (I prefer the following 'xlUp' approach):
    Dim LastRow As Long
    LastRow = Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Row
    Set CopyRange = Sheet1.Range("B2:B" & LastRow)
    
    Debug.Print "CopyRange:", Sheet1.Name, CopyRange.Address
    
' Define PasteCell.
    
    Dim FirstEmptyRow As Long
    FirstEmptyRow = Sheet2.Cells(Sheet2.Rows.Count, "B").End(xlUp).Row + 1
    
    Dim PasteCell As Range
    Set PasteCell = Sheet2.Range("B" & FirstEmptyRow)
    
    Debug.Print "PasteCell:", Sheet2.Name, PasteCell.Address
    
' The CopyPaste
    
    ' 1st Version
    ' This version is the most efficient of these 3 versions, but it can
    ' only be used for copying values (not formulas, formats... etc.).
    
    ' Either...
    'PasteCell.Resize(CopyRange.Rows.Count, CopyRange.Columns.Count).Value _
      = CopyRange.Value
    
    ' ...or:
    ' To better understand it, you can define a 'PasteRange'.
    Dim PasteRange As Range
    Set PasteRange = PasteCell.Resize(CopyRange.Rows.Count, _
                                      CopyRange.Columns.Count)
    PasteRange.Value = CopyRange.Value
    Debug.Print "PasteRange:", Sheet2.Name, PasteRange.Address
    
'    ' 2nd Version
'    ' This version will copy 'everything', including formulas, formats etc.

'    ' The following line...
'    CopyRange.Copy PasteCell

'    ' is just short for:
'    CopyRange.Copy Destination:=PasteCell

'    ' 3rd Version
'    ' This version will do the same as the previous, but as a downside,
'    ' it will change the selection on the 'PasteSheet' to 'PasteRange' and
'    ' will set the 'CutCopyMode' to 'xlCopy'. But as an upside, you can
'    ' choose what you want to copy by 'adding arguments' to 'PasteSpecial'.
'    ' Put the cursor right behind 'PasteSpecial' and press 'SPACE' to
'    ' see the various arguments and their parameters.

'    CopyRange.Copy
'    PasteCell.PasteSpecial
'    Application.CutCopyMode = False
    
End Sub

Copy a Range From One Worksheet To The Last Row of Data On Another

2 Answers2

Copy Range