Appending filtered range to sheet

Question

With this code I simply (Thanks to Slai) copy and paste a filtered range from one sheet to another. However I attempted to replicate this code by appending another filtered range to the HAA sheet but for some reason it selects row 11 and pastes the data.

Sub Run()

    Application.ScreenUpdating = False

    Dim x As Long
    Dim rf As Range, wsTo As Worksheet, wx As Range

    Set rf = ThisWorkbook.Sheets("Table").UsedRange
    Set wsTo = Sheets("HAA")
    Set wx = ThisWorkbook.Sheets("HAA").UsedRange

    x = Range("B" & Rows.Count).End(xlUp).Row

    rf.AutoFilter
    rf.AutoFilter 12, "associated"
    rf.Copy

    wsTo.Range("A1").PasteSpecial xlPasteValues

    rf.AutoFilter
    rf.AutoFilter 12, "not found"
    rf.Offset(1, 0).Copy

I changed this (below) to select to see where it was pasting the data, originally had .PasteSpecial xlPasteValues

    wsTo.Range("A1" & x).Select

    Application.ScreenUpdating = True

End Sub

What worksheet are you trying to get the last populated row (e.g. x) from? Why are you not offsetting to get the first available blank cell? e.g. x = wsTo.Range("B" & Rows.Count).End(xlUp).OFFSET(1, 0).Row It looks like it should be wsTo.Range("A" & x).Select. — user4039065
@Jeeped x = wsTo.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row With this, it pastes over the existing data. — FonR

Slai Slai · Accepted Answer · 2016-09-16T16:39:17

To paste after the last row, something like:

Dim rangeTo As Range
Set rangeTo = wsTo.UsedRange ' or = wsTo.Range("A1").CurrentRegion
Set rangeTo = rangeTo.Offset(rangeTo.Rows.Count)(1) ' (1) is to get the first cell 
Application.Goto rangeTo ' optional if you want to see where the values will be pasted
rangeTo.PasteSpecial xlPasteValues

The problem in your code is that you get the last row before you paste and filter the data. Also, Range( usually refers to the currently active Sheet which might not always be the one you want, so it's better to specify the sheet before the Range:

 x = wsTo.Range("B" & wsTo.Rows.Count).End(xlUp).Row

Appending filtered range to sheet

1 Answers