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

Lingots d or

New Member
Joined
Jan 3, 2019
Messages
2
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Lingots d or

New Member
Joined
Jan 3, 2019
Messages
2
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.

Code:
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
 

Forum statistics

Threads
1,141,070
Messages
5,704,112
Members
421,327
Latest member
Msh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top