Extract Data by Advanced Filter & VBA

Extract Data by Advanced Filter & VBA

By Using Advanced Filter & VBA, we can filter our data for multiple options in a separate sheet.

Step1: Have you Raw Data (like given below) in a sheet namely RawData and create a table of it by pressing Ctrl L after selecting the data. The default table name would be Table1.

Date	Item	Client	SalesPerson
Monday, November 04, 2013	Mobile	Jim Smith	Peter
Wednesday, November 13, 2013	LapTop	Nadeem	Alyson
Saturday, November 16, 2013	PC	Saeed	Peter
Monday, November 25, 2013	LCD	Jim Smith	Alyson
Tuesday, December 03, 2013	Table	Nadeem	Alyson
Saturday, December 07, 2013	Table	Saeed	Alyson
Wednesday, December 11, 2013	Mobile	Jim Smith	Peter
Sunday, December 15, 2013	LapTop	Nadeem	Alyson
Thursday, December 19, 2013	PC	Saeed	Peter
Monday, December 23, 2013	LCD	Jim Smith	Alyson
Friday, December 27, 2013	Table	Nadeem	Alyson

Step2: In another sheet like in Sheet3 at top have your criteria or options with drop down box.

Suppose against the Item Client in the cell you want in drop down to appear Jim Smith, Nadeem, Saeed and a blank option. For that have your options like in this case the names in a separate sheet. Then select the cell where you want the drop down, go to Data tab and click on Data Validation. On the settings tab in drop down of Allow: select List. In the source provide the range where options items are like if in sheet2’s cells A2:A3 have the client names and cell A4 is blank then go to that sheet and select the range A2:A4. This way, we will be able to show a blank option as well.

Macro to run advanced filter and extract data

First part of the code is to make sure that the current filtered data if any is cleared out before we run the macro again.

The second part of the code is for copying the criteria we select in drop down and paste (special transpose) in the RawData sheet. This is the criteria that our final part of code would be using to filter data.

Put a button and assign this macro to it. For this first save the below code, by going to Developer Tab and then clicking the Visual Basic. A new window will open now click on Insert > Module, paste the below code there save and close the window. Now for putting a button in your sheet (i.e. Sheet 3 for this example) go to Developer tab then Insert then Click on button (Form Control). Place it where you want.

Now right click on it and click on Assign Macro and assign this Filterdata macro to that button. So now after you select your criteria, clicking on this button will run the macro.

One major benefit here is that you can run for all the three options after selecting them or load all data by selecting all options as blank or can extract data for any one or two items as well. Please try it and in case of any issue, please contact me.

Sub FilterData()

    Sheets("Sheet3").Select

    Range("B10").Select

    Range(Selection, Selection.End(xlToRight)).Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.Clear

   

     Sheets("Sheet3").Select

        Range("A2:B4").Select

    Selection.Copy

    Sheets("RawData").Select

    Range("M1").Select

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

        False, Transpose:=True

    Sheets("Sheet3").Select   

   

    Sheets("RawData").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _

        Sheets("RawData").Range("M1:O2"), CopyToRange:=Sheets("Sheet3").Range("B10"), Unique:=True

    Columns.AutoFit

    Range("B10").Select

     Sheets("Sheet3").Select

End Sub