xcellrodeo
Board Regular
- Joined
- Oct 27, 2008
- Messages
- 206
Dear MrExcel Community, I would like to request some help with the below script.I have a data set in Excel that starts with row 13 and has 26 columns. The headers are in row 12.
A pop-up user form requires the user to select from a drop down menu i.e Top50, Top100, Top200...
Once this is selected, the script below applies a filter to the header row & filters Column O based on the Topxxx chosen in the user form.
The script then selects the visible cells in selected columns from the data set and copies these to another sheet.
The only thing that changes is the filter criteria i.e the user-defined choice of Topxxx from the user form.
Obviously I would like to try and avoid having to copy copious lines of coding for each filter criteria i.e Top50, Top100 etc etc.
I hope this makes sense & you can help. many thanks in advance
A pop-up user form requires the user to select from a drop down menu i.e Top50, Top100, Top200...
Once this is selected, the script below applies a filter to the header row & filters Column O based on the Topxxx chosen in the user form.
The script then selects the visible cells in selected columns from the data set and copies these to another sheet.
The only thing that changes is the filter criteria i.e the user-defined choice of Topxxx from the user form.
Obviously I would like to try and avoid having to copy copious lines of coding for each filter criteria i.e Top50, Top100 etc etc.
I hope this makes sense & you can help. many thanks in advance
Code:
If UserForm1.ComboBox2 = "Top50" Then
Sheets("MD14").Activate
A9 = ActiveSheet.Range("A9").Value 'Counta
ActiveSheet.Range("A12:Z12").Select
Selection.AutoFilter
ActiveSheet.Range("A12").Resize(A9, 26).AutoFilter Field:=15, Criteria1:="Top50"
ActiveSheet.Range("A13").Resize(A9, 1).SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("RM").Activate
ActiveSheet.Range("C12").Select
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("MD14").Activate
A9 = ActiveSheet.Range("A9").Value 'Counta
ActiveSheet.Range("E13").Resize(A9, 2).SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("RM").Activate
ActiveSheet.Range("D12").Select
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("MD14").Activate
A9 = ActiveSheet.Range("A9").Value 'Counta
ActiveSheet.Range("N13").Resize(A9, 2).SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("RM").Activate
ActiveSheet.Range("G12").Select
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False