VBA Express : Excel - Filter based on selection in two columns and copy to a new sheet

Excel

Filter based on selection in two columns and copy to a new sheet

Ease of Use

Easy

Version tested with

2000

Submitted by:

lucas

Description:

This will copy rows to a new sheet based on 2 selections in a validation list.

Discussion:

You wish to filter data according to a selection of projects and packages where sometimes the project has more than one package but you wish to filter by project and package.

Code:

instructions for use

			Put this In a standard module: 
 
Option Explicit 
Sub Advanced_Filter() 
     'select the range to filter based on selection in the range F1:G2
     'and copy to the sheet named "Print"
    Range("A4:K30").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ 
    "F1:G2"), CopyToRange:=Range("Print!A3"), Unique:=False 
    Sheets("Print").Select 
    Range("A1").Select 
End Sub

How to use:

Open the Visual Basic Editor by going to tools-Macro's-Visual Basic Editor or use Alt-F11
On the toolbar of the Visual Basic Editor, go to insert - module
In the module pane paste the code above.
Close the Visual Basic Editor By clicking the X in the upper right corner or go to File-Close
Set up your validation list and make it the same as the code.(you don't have to use a validation list, you can just type in the project and package)

Test the code:

Select The project and package you wish to filter out.
Run the macro by going to Toos-Macro's-Macro and select the Advanced_Filter macro
Click on run.
Your selection will be filtered and copied to the sheet "Print"
You can now copy that sheet to a new location and rename it or print it.

Sample File:

filter_and_copy.zip 12.87KB

Approved by mdmackillop

This entry has been viewed 328 times.