| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter Pull Criteria from one sheet and custom filter on other
I'm trying to Custom Autofilter on a sheet by pulling the criteria from a
drop down list on another sheet. Here is how I have been trying to do it. fi_mgr_num = Sheets("reports").Range("I6") Selection.AutoFilter Field:=2, Criteria1:="=" & fi_mgr_num, Operator:=xlAnd I don't have a clue if this is close, but I saw some of these ideas here on the forum and have been trying various interations to make it work. Any help would be appreciated. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoFilter Pull Criteria from one sheet and custom filter on other
Hi DTG,
fi_mgr_num = Sheets("reports").Range("I6") Selection.AutoFilter Field:=2, Criteria1:="=" & fi_mgr_num, Operator:=xlAnd This worked for me, provided that a cell in the autofilter range was pre-selected. If the first autofilter column only had a single criterion, you could dispense with: , Operator:=xlAnd Did you try it and, if so, what problems did you experience? --- Regards, Norman "dtg_denver" wrote in message ... I'm trying to Custom Autofilter on a sheet by pulling the criteria from a drop down list on another sheet. Here is how I have been trying to do it. fi_mgr_num = Sheets("reports").Range("I6") Selection.AutoFilter Field:=2, Criteria1:="=" & fi_mgr_num, Operator:=xlAnd I don't have a clue if this is close, but I saw some of these ideas here on the forum and have been trying various interations to make it work. Any help would be appreciated. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoFilter Pull Criteria from one sheet and custom filter on o
I'm not real sure what you are asking.
The range I'm trying to filter is on worksheet, "New", column "fi_mgr". Do I need to have the macro select a cell on the "new" sheet in the "fi_mgr" column? "Norman Jones" wrote: Hi DTG, fi_mgr_num = Sheets("reports").Range("I6") Selection.AutoFilter Field:=2, Criteria1:="=" & fi_mgr_num, Operator:=xlAnd This worked for me, provided that a cell in the autofilter range was pre-selected. If the first autofilter column only had a single criterion, you could dispense with: , Operator:=xlAnd Did you try it and, if so, what problems did you experience? --- Regards, Norman "dtg_denver" wrote in message ... I'm trying to Custom Autofilter on a sheet by pulling the criteria from a drop down list on another sheet. Here is how I have been trying to do it. fi_mgr_num = Sheets("reports").Range("I6") Selection.AutoFilter Field:=2, Criteria1:="=" & fi_mgr_num, Operator:=xlAnd I don't have a clue if this is close, but I saw some of these ideas here on the forum and have been trying various interations to make it work. Any help would be appreciated. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoFilter Pull Criteria from one sheet and custom filter on o
Hi DTG,
Do I need to have the macro select a cell on the "new" sheet in the "fi_mgr" No. Delections are rarely necessary an are usually to be avoided. My comment related to tour code line: Selection.AutoFilter Field:=2, Criteria1:="=" & fi_mgr_num, With this syntax, an autofilter cell selection wouls be required. The following does not requre any selection: Sub Tester01() Dim WB As Workbook Dim RngFilter As Range Dim fi_mgr_num As String Set WB = ActiveWorkbook '<<==== CHANGE to suit Set RngFilter = Sheets("New").AutoFilter.Range fi_mgr_num = _ Sheets("Sheet3").Range("A1").Value '<<==== CHANGE to suit RngFilter.AutoFilter Field:=1, Criteria1:=fi_mgr_num End Sub --- Regards, Norman "dtg_denver" wrote in message ... I'm not real sure what you are asking. The range I'm trying to filter is on worksheet, "New", column "fi_mgr". Do I need to have the macro select a cell on the "new" sheet in the "fi_mgr" column? "Norman Jones" wrote: Hi DTG, fi_mgr_num = Sheets("reports").Range("I6") Selection.AutoFilter Field:=2, Criteria1:="=" & fi_mgr_num, Operator:=xlAnd This worked for me, provided that a cell in the autofilter range was pre-selected. If the first autofilter column only had a single criterion, you could dispense with: , Operator:=xlAnd Did you try it and, if so, what problems did you experience? --- Regards, Norman "dtg_denver" wrote in message ... I'm trying to Custom Autofilter on a sheet by pulling the criteria from a drop down list on another sheet. Here is how I have been trying to do it. fi_mgr_num = Sheets("reports").Range("I6") Selection.AutoFilter Field:=2, Criteria1:="=" & fi_mgr_num, Operator:=xlAnd I don't have a clue if this is close, but I saw some of these ideas here on the forum and have been trying various interations to make it work. Any help would be appreciated. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoFilter Pull Criteria from one sheet and custom filter on o
Norman,
This worked great. Thanks a million Regards, Mark "Norman Jones" wrote: Hi DTG, Do I need to have the macro select a cell on the "new" sheet in the "fi_mgr" No. Delections are rarely necessary an are usually to be avoided. My comment related to tour code line: Selection.AutoFilter Field:=2, Criteria1:="=" & fi_mgr_num, With this syntax, an autofilter cell selection wouls be required. The following does not requre any selection: Sub Tester01() Dim WB As Workbook Dim RngFilter As Range Dim fi_mgr_num As String Set WB = ActiveWorkbook '<<==== CHANGE to suit Set RngFilter = Sheets("New").AutoFilter.Range fi_mgr_num = _ Sheets("Sheet3").Range("A1").Value '<<==== CHANGE to suit RngFilter.AutoFilter Field:=1, Criteria1:=fi_mgr_num End Sub --- Regards, Norman "dtg_denver" wrote in message ... I'm not real sure what you are asking. The range I'm trying to filter is on worksheet, "New", column "fi_mgr". Do I need to have the macro select a cell on the "new" sheet in the "fi_mgr" column? "Norman Jones" wrote: Hi DTG, fi_mgr_num = Sheets("reports").Range("I6") Selection.AutoFilter Field:=2, Criteria1:="=" & fi_mgr_num, Operator:=xlAnd This worked for me, provided that a cell in the autofilter range was pre-selected. If the first autofilter column only had a single criterion, you could dispense with: , Operator:=xlAnd Did you try it and, if so, what problems did you experience? --- Regards, Norman "dtg_denver" wrote in message ... I'm trying to Custom Autofilter on a sheet by pulling the criteria from a drop down list on another sheet. Here is how I have been trying to do it. fi_mgr_num = Sheets("reports").Range("I6") Selection.AutoFilter Field:=2, Criteria1:="=" & fi_mgr_num, Operator:=xlAnd I don't have a clue if this is close, but I saw some of these ideas here on the forum and have been trying various interations to make it work. Any help would be appreciated. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoFilter Pull Criteria from one sheet and custom filter on o
I have two sheets I need to run this filter on. I thought once I had the
first sheet I could just repeat the process, but that doesn't seem to work. The code works fine on the first sheet but not on the second. And it's not throsing any errors. Dim WB As Workbook Dim RangeFilter As Range Dim fi_mgr_num As String Set WB = ThisWorkbook Set RngFilter = Sheets("New").AutoFilter.Range fi_mgr_num = Sheets("Reports").Range("K6").Value RngFilter.AutoFilter Field:=2, Criteria1:=fi_mgr_num Set WB = ThisWorkbook Set RngFilter = Sheets("Used").AutoFilter.Range fi_mgr_num = Sheets("Reports").Range("K12").Value RngFilter.AutoFilter Field:=2, Criteria1:=fi_mgr_num "dtg_denver" wrote: Norman, This worked great. Thanks a million Regards, Mark "Norman Jones" wrote: Hi DTG, Do I need to have the macro select a cell on the "new" sheet in the "fi_mgr" No. Delections are rarely necessary an are usually to be avoided. My comment related to tour code line: Selection.AutoFilter Field:=2, Criteria1:="=" & fi_mgr_num, With this syntax, an autofilter cell selection wouls be required. The following does not requre any selection: Sub Tester01() Dim WB As Workbook Dim RngFilter As Range Dim fi_mgr_num As String Set WB = ActiveWorkbook '<<==== CHANGE to suit Set RngFilter = Sheets("New").AutoFilter.Range fi_mgr_num = _ Sheets("Sheet3").Range("A1").Value '<<==== CHANGE to suit RngFilter.AutoFilter Field:=1, Criteria1:=fi_mgr_num End Sub --- Regards, Norman "dtg_denver" wrote in message ... I'm not real sure what you are asking. The range I'm trying to filter is on worksheet, "New", column "fi_mgr". Do I need to have the macro select a cell on the "new" sheet in the "fi_mgr" column? "Norman Jones" wrote: Hi DTG, fi_mgr_num = Sheets("reports").Range("I6") Selection.AutoFilter Field:=2, Criteria1:="=" & fi_mgr_num, Operator:=xlAnd This worked for me, provided that a cell in the autofilter range was pre-selected. If the first autofilter column only had a single criterion, you could dispense with: , Operator:=xlAnd Did you try it and, if so, what problems did you experience? --- Regards, Norman "dtg_denver" wrote in message ... I'm trying to Custom Autofilter on a sheet by pulling the criteria from a drop down list on another sheet. Here is how I have been trying to do it. fi_mgr_num = Sheets("reports").Range("I6") Selection.AutoFilter Field:=2, Criteria1:="=" & fi_mgr_num, Operator:=xlAnd I don't have a clue if this is close, but I saw some of these ideas here on the forum and have been trying various interations to make it work. Any help would be appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I filter for a blank cell, using custom autofilter | Excel Discussion (Misc queries) | |||
| AutoFilter: custom filter hides all | Excel Worksheet Functions | |||
| saving autofiltercustom filter settings | Excel Worksheet Functions | |||
| can custom autofilter pull parameters from other data? | Excel Discussion (Misc queries) | |||
| Shortcut for Filter- Custom Autofilter | Excel Discussion (Misc queries) | |||
Hybrid Mode
