Copy data from a filtered selection to the first empty rows on another sheet

[Lingots d or]

Hi all,

This one may be easy for you, but I've only been doing macros for about a week now and need help on it. (I managed to automatize a bit of my work already with minimal knowledge and I'm very enthusiast about the power of VBA and want to do more).

This particular thread is for these actions:

- I have sheets SOURCE and DESTINATION in different files

- in file SOURCE I filter column P for value <2 (will take 1 or 0)

- now from the filtered data I copy column A from SOURCE to column A in DESTINATION starting on the first available row (this sheet has thousands of rows as I add new ones daily)

- from the filtered data I also copy columns C to N from SOURCE to columns D to O in DESTINATION, the same as above, starting on the first available row

Thank you.

 

[Lingots d or]

I've managed to copy the data I need from source file to destination file by duplicating the code below for each column to be copied and for each sheet I copy from.

I still have one issue: although it works great when it does find values <2 when filtered as shown below, when it does not find any such value it copies all the rows - how can I ask the macro not to take any action when the search criteria is not met? Thanks.

Windows("SOURCE.xlsx").ActivateSheets("SHEET1").Select
ActiveSheet.Range("$A$1:$Q$100").AutoFilter Field:=16, Criteria1:="<2", Operator:=xlAnd

ActiveSheet.Columns("A:A").SpecialCells(xlCellTypeConstants, 23).Offset(1, 0).Select
Selection.Copy
    Windows("DESTINATION.xlsx").Activate
    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False