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