If you find any post useful then, please do share with others. Thanks!

Popular Posts

Contact

Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Wednesday, October 30, 2013

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

No comments:

Post a Comment