Pasting under original data at the bottom of the column in VBA

Question

I am trying to copy and paste data from one sheet to another but under the current data. The destination for the paste will vary as I want it to be pasted below the last row of data already in that range. I start by filtering data in the origin sheet, copying it and pasting it in destination sheet. For example I want the new data (B2:D & lastrow) from origin sheet to be pasted in columns (G:I & newlastrow) in the destination sheet under the current data in there.

I have tried the following and currently I am getting a problem with the range.

Sub copytoderiv()

Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
ActiveWorkbook.Sheets("origin").Activate
Call TURNFILTERSOFF


Sheets("origin").Range("$A$1:$D$50000").AutoFilter Field:=1, Criteria1:="XXX"
        
ActiveSheet.Range("B2:D" & lastrow).SpecialCells(xlCellTypeVisible).Copy
    
Dim Newlastrow As String
ActiveWorkbook.Sheets("destination").Activate
Newlastrow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row + 1
Range("G:I" & Newlastrow).Select
ActiveSheet.PasteSpecial
 
Call TURNFILTERSOFF
    
    
    
End Sub

I see, I changed `newlastrow` to `long` and range to `Range("G2:I" & Newlastrow)` and now it is pasting in the right columns but over the current data at the top in G2 and not below the current data — Starbucks, Sep 11 '20 at 20:22
... because you included `2` when you want to use `Newlastrow`? — BigBen, Sep 11 '20 at 20:23
Without it I get method range of object global failed, I'm not sure what else to try — Starbucks, Sep 11 '20 at 20:26
`Range("G" & Newlastrow).Select` but you really [should avoid using `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Sep 11 '20 at 20:27
Thank you! and thanks for the resource, I am going to work out how to avoid using select in this scenario — Starbucks, Sep 11 '20 at 20:30
Is the code located in the `ActiveWorkbook` or in another workbook? Can we assume that `TURNFILTERSOFF` is a procedure that is working correctly? — VBasic2008, Sep 11 '20 at 22:43

score 1 · Accepted Answer · answered Sep 11 '20 at 23:43

`Copy` With `Autofilter` and `SpecialCells`

Links (Microsoft)

A Quick Fix

Option Explicit

Sub copytoderiv()

    ' Define Workbook.  
    Dim wb As Workbook
    Set wb = ThisWorkbook ' The worksheet containing this code.
    
    ' Define Original Worksheet and its Last Row.
    Dim orig As Worksheet: Set orig = wb.Worksheets("origin")
    Dim LastRow As Long
    LastRow = orig.Cells(orig.Rows.Count, "A").End(xlUp).Row
    
    ' Define Destination Worksheet and its Start Row.
    Dim dest As Worksheet: Set dest = wb.Worksheets("destination")
    Dim StartRow As String
    StartRow = dest.Cells(dest.Rows.Count, "H").End(xlUp).Row + 1
    
    orig.Activate
    TURNFILTERSOFF
    
    ' Filter data.
    orig.Range("A1:D" & LastRow).AutoFilter Field:=1, Criteria1:="XXX"
    
    ' Either:
    ' Copy values and formats.
    orig.Range("B2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy _
      dest.Range("G" & StartRow)
    
    ' Or:
    ' Copy values, formulas and formats.
'    orig.Range("B2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy
'    'Study the PasteSpecial method thorrowly because there are many options.
'    dest.Range("G" & StartRow).PasteSpecial
'    Application.CutCopyMode = False
     
    TURNFILTERSOFF
    
End Sub

Pasting under original data at the bottom of the column in VBA

1 Answers1

Copy With Autofilter and SpecialCells

`Copy` With `Autofilter` and `SpecialCells`