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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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,136,776
Messages
5,677,671
Members
419,711
Latest member
dacrmcvega0

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