Suppose you are asked to add a combo box (drop down) in your sheet and pull values from a range based on selection in the combo box (drop down).
How to add a combo box (drop down)
- Check if Developer tab is enabled.
If it is not enabled, you need to add Developer Tab. To add Developer tab into Excel 2010 and 2013, follow the instructions below:
- Click the File tab
- Click the Options tab
- Click the Customize Ribbon at the left
- At the right, select the Main Tabs from Customize The Ribbon drop down box
- Check the Developer item
- Click the OK button.
2. On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Combo box Button.
Create dummy data
Enter data into column A to C.
Task : Filter data based on column A values listed in drop down.
- Right click on the drop down and select Format Control and then go to Control tab
- In the Input range: enter A2:A6 (values to display in the drop-down list).
- In the Cell link:, enter D1 (cell reference that tells you which value is selected in the drop-down list).
- Press ALT + F11 to open VBA editor window.
- Click on Insert Tab and then click Module option
- In the module, paste the following code
Sub FilterData()
'Get the Name of ComboBox (Drop Down)
Cbo = Application.Caller
'Get the line number of the entry and the entry data
With Sheets("Sheet1").Shapes(Cbo).ControlFormat
CboLine = .ListIndex
cboData = .List(CboLine)
End With
' turn off any autofilters that are already set
Sheets("Sheet1").AutoFilterMode = False
' filter column A based on combo box selection
ActiveSheet.UsedRange.AutoFilter 1, cboData
' copy the visible cells to cell E8
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Copy Range("E8")
' Turn off any autofilters that are already set
Sheets("Sheet1").AutoFilterMode = False
End Sub
Next Step : Right Click on the drop down and click Assign Macro and select FilterData and then click OK.
Customize the above code
1. Copy visible cells from one column of a filtered range
ActiveSheet.UsedRange.AutoFilter 1, "Sandy"
rng.SpecialCells(xlCellTypeVisible).Copy
2. Customize filter range - Filter only two columns
lastRow = Sheets("Sheet1").Range("A" & Sheets("Sheet1").Rows.Count).End(xlUp).Row
Set filterRange = Sheets("Sheet1").Range("A1:B" & lastRow)
' In this case we are filtering Deepanshu from column A
filterRange.AutoFilter 1, "Deepanshu"
3. Copy data only when at least one record is available after applying filter
Sheets("Sheet1").UsedRange.AutoFilter 1, cboData
Nrows=Sheets("Sheet1").Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count
If Nrows < 2 Then
MsgBox ("No record available")
Else
' Your code
4. Copy data from second row (Excluding header)
Sheets("sheet1").UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).CopyRelated Link - Filtering and Pasting Data to New Sheet
Great! Had been looking for something like this all over the internet...
ReplyDeleteFantastic!
ReplyDeleteThank you for this example! Is there a way to do it so that you can erase the previously copied data with the new data based on a new selection?
ReplyDeleteNICE
ReplyDelete