Copy filtered rows from file to open workbook

Question

I'm trying to open a currently closed workbook, filter column A based on Sel_RC (A2 is the header), copy only the visible rows and paste into ThisWorkbook, sheet "LRD".

Sub Get_RC_Data()

Dim wbSource As Workbook, wbDest As Workbook
Dim wsSource As Worksheet, wsDest As Worksheet
Dim rngSource As Range, rngDest As Range
Dim Sel_RC As Range

Set wbDest = ThisWorkbook
Set wsDest = wbDest.Worksheets("LRD")
Set rngDest = wsDest.Range("A:CY")

Set Sel_RC = wbDest.Worksheets("Summary").Range("B2")

Set wbSource = Workbooks.Open("G:\Folder\File.xlsm")
Set wsSource = wbSource.Worksheets("data")
wsSource.Range("A2").AutoFilter Field:=1, Criteria1:=Sel_RC
Set rngSource = wsSource.Range(wsSource.Range("A2:CY2"), 
wsSource.Range("A2:CY2").End(xlDown)).SpecialCells(xlCellTypeVisible)

rngDest.Value = rngSource.Value

wbSource.Close (False)

End Sub

Everything works fine except for the line:

Set rngSource = wsSource.Range(wsSource.Range("A2:CY2"), wsSource.Range("A2:CY2").End(xlDown)).SpecialCells(xlCellTypeVisible)

The problem I'm having is I can't get the source range to select only the visible filtered rows. I've tried a combination of the above - the current one copies row 2 into every row of the destination sheet. I've also had similar semi success with:

Set rngSource = wsSource.Range("A:CY")

which copied and pasted correctly but ignored the filter (i.e. just copied all rows from the source).

Thanks.

You have to iterate over the areas in rngSource. rngDest.Value = rngSource.Value doesn't work for composite ranges. — Excel Developers

Andy G Andy G · Accepted Answer · 2018-01-05T11:35:37

Split the range to the top-left and bottom-right cells only, then Copy the visible cells:

Set rngSource = wsSource.Range(wsSource.Range("A2"), 
    wsSource.Range("CY2").End(xlDown)).SpecialCells(xlCellTypeVisible)

rngSource.Copy

wsDest.Activate
rngDest.Cells(1, 1).Select
ActiveSheet.Paste

OR

Set rngSource = wsSource.Range(wsSource.Range("A2"), 
    wsSource.Range("CY2").End(xlDown))

rngSource.SpecialCells(xlCellTypeVisible).Copy

wsDest.Activate
rngDest.Cells(1, 1).Select
ActiveSheet.Paste

Copy filtered rows from file to open workbook

1 Answers