|
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. |
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. |
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. |
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. |
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. |
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. |
AutoFilter Pull Criteria from one sheet and custom filter on o
Hi DTG,
The following worked for me: '====================== Public Sub Tester04() Dim WB As Workbook Dim RngFilter As Range Dim RngFilter2 As Range Dim fi_mgr_num As String Dim fi_mgr_num2 As String Set WB = ThisWorkbook Set RngFilter = WB.Sheets("New").AutoFilter.Range fi_mgr_num = Sheets("Reports").Range("K6").Value RngFilter.AutoFilter Field:=2, Criteria1:=fi_mgr_num Set RngFilter2 = WB.Sheets("Used").AutoFilter.Range fi_mgr_num2 = Sheets("Reports").Range("K12").Value RngFilter2.AutoFilter Field:=2, Criteria1:=fi_mgr_num2 End Sub '<<'====================== --- Regards, Norman "dtg_denver" wrote in message ... 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. |
AutoFilter Pull Criteria from one sheet and custom filter on o
I thought it might work something like that but since I don't really know
what I'm doing, I'm a little skitsh about getting to far ahead of my skills. I'll give it a try. Thanks. "Norman Jones" wrote: Hi DTG, The following worked for me: '====================== Public Sub Tester04() Dim WB As Workbook Dim RngFilter As Range Dim RngFilter2 As Range Dim fi_mgr_num As String Dim fi_mgr_num2 As String Set WB = ThisWorkbook Set RngFilter = WB.Sheets("New").AutoFilter.Range fi_mgr_num = Sheets("Reports").Range("K6").Value RngFilter.AutoFilter Field:=2, Criteria1:=fi_mgr_num Set RngFilter2 = WB.Sheets("Used").AutoFilter.Range fi_mgr_num2 = Sheets("Reports").Range("K12").Value RngFilter2.AutoFilter Field:=2, Criteria1:=fi_mgr_num2 End Sub '<<'====================== --- Regards, Norman "dtg_denver" wrote in message ... 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. |
AutoFilter Pull Criteria from one sheet and custom filter on o
Works Great !!! Now I only have to repeat the code for two more columns.
Thanks. "Norman Jones" wrote: Hi DTG, The following worked for me: '====================== Public Sub Tester04() Dim WB As Workbook Dim RngFilter As Range Dim RngFilter2 As Range Dim fi_mgr_num As String Dim fi_mgr_num2 As String Set WB = ThisWorkbook Set RngFilter = WB.Sheets("New").AutoFilter.Range fi_mgr_num = Sheets("Reports").Range("K6").Value RngFilter.AutoFilter Field:=2, Criteria1:=fi_mgr_num Set RngFilter2 = WB.Sheets("Used").AutoFilter.Range fi_mgr_num2 = Sheets("Reports").Range("K12").Value RngFilter2.AutoFilter Field:=2, Criteria1:=fi_mgr_num2 End Sub '<<'====================== --- Regards, Norman "dtg_denver" wrote in message ... 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. |
| All times are GMT +1. The time now is 12:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com