Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set a button to filter a table using specific from and tocells data
I have a spreadsheet with 2 tabs. "Instr" and "BF_List". On the
Instr tab, I have a table to generate the 12 months of the year with from and to dates based on cell T69. This way I can have dates for years 2009 to 2020. The table has the dates in cells T71 (ie. Jan. 1, 2009) and cell U71 has Jan. 31, 2009. Down the table to Dec. Now on the tab BF list, starting on row 4 I have effective date, name and other details up to column F. This list could have assorted dates from Jan. 1, 2009 to Dec. 31, 2009. I want to be able to give the users an easy option to sort by month. So, I had thought adding a button for each month. ie. sort Jan. sort Feb. and so on. My problem is the code for the macro to sort. I need to be able to pull from the first tab on the Instr. sheet the from and to dates so that I can filter the list. I have chosen 'equal to or greater than" and cell T71 for Jan. 1, 2009 and then then end date of 'less than or equal to" cell U71. I would change cells for each month. I have not found a way to get the macro code to select from the table on the other tab. The table I want to sort has a header row A3 to F3 and the data is from A4 to F4 down to row 4000. this is what I have so far. Range("A4").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=01-Jan-2009", Operator:= _ xlAnd, Criteria2:="<=31-jan-2009" End Sub thanks for any help or options. Mel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set a button to filter a table using specific from and tocells data
On Feb 19, 9:27*am, "Don Guillett" wrote:
I recommend putting dates in a cell with the same format as your column. Sub filterbydatesinrange() Range("a4).AutoFilter Field:=1, Criteria1:="=" & Range("l3"), _ Operator:=xlAnd, Criteria2:="<=" & Range("l4") End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... I have a spreadsheet with 2 tabs. *"Instr" and "BF_List". *On the Instr tab, I have atableto generate the 12 months of the year with from and to dates based on cell T69. *This way I can have dates for years 2009 to 2020. *Thetablehas the dates in cells T71 (ie. Jan. 1, 2009) and cell U71 has Jan. 31, 2009. *Down thetableto Dec. Now on the tab BF list, starting on row 4 I have effective date, name and other details up to column F. * This list could have assorted dates from Jan. 1, 2009 to Dec. 31, 2009. * I want to be able to give the users an easy option to sort by month. *So, I had thought adding a buttonfor each month. *ie. sort Jan. sort Feb. and so on. My problem is the code for the macro to sort. *I need to be able to pull from the first tab on the Instr. sheet the from and to dates so that I canfilterthe list. *I have chosen 'equal to or greater than" and cell T71 for Jan. 1, 2009 and then then end date of 'less than or equal to" cell U71. I would change cells for each month. I have not found a way to get the macro code to select from thetable on the other tab. ThetableI want to sort has a header row A3 to F3 and the data is from A4 to F4 down to row 4000. this is what I have so far. * Range("A4").Select * *Selection.AutoFilter * *Selection.AutoFilter Field:=1, Criteria1:="=01-Jan-2009", Operator:= _ * * * *xlAnd, Criteria2:="<=31-jan-2009" End Sub thanks for any help or options. Mel- Hide quoted text - - Show quoted text - Works excelent. I knew it was simple but could not figure it out. thanks so much Mel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set a button to filter a table using specific from and to cells data
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set a button to filter a table using specific from and tocells data
On Feb 19, 1:33*pm, "Don Guillett" wrote:
Glad to help -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... On Feb 19, 9:27 am, "Don Guillett" wrote: I recommend putting dates in a cell with the same format as your column.. Sub filterbydatesinrange() Range("a4).AutoFilterField:=1, Criteria1:="=" & Range("l3"), _ Operator:=xlAnd, Criteria2:="<=" & Range("l4") End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message .... I have a spreadsheet with 2 tabs. "Instr" and "BF_List". On the Instr tab, I have atableto generate the 12 months of the year with from and to dates based on cell T69. This way I can have dates for years 2009 to 2020. Thetablehas the dates in cells T71 (ie. Jan. 1, 2009) and cell U71 has Jan. 31, 2009. Down thetableto Dec. Now on the tab BF list, starting on row 4 I have effective date,name and other details up to column F. This list could have assorted dates from Jan. 1, 2009 to Dec. 31, 2009. I want to be able to give the users an easy option tosortby month. So, I had thought adding a buttonfor each month. ie.sortJan.sortFeb. and so on. My problem is the code for the macro tosort. I need to be able to pull from the first tab on the Instr. sheet the from and to dates so that I canfilterthe list. I have chosen 'equal to or greater than" and cell T71 for Jan. 1, 2009 and then then end date of 'less than or equal to" cell U71. I would change cells for each month. I have not found a way to get the macro code to select from thetable on the other tab. ThetableI want tosorthas a header row A3 to F3 and the data is from A4 to F4 down to row 4000. this is what I have so far. Range("A4").Select Selection.AutoFilter Selection.AutoFilterField:=1, Criteria1:="=01-Jan-2009", Operator:= _ xlAnd, Criteria2:="<=31-jan-2009" End Sub thanks for any help or options. Mel- Hide quoted text - - Show quoted text - Works excelent. *I knew it was simple but could not figure it out. thanks so much Mel I am also trying to do something similar, but I have a table in excel 2007 and since the table is linked to an sql query, the order of the columns are not always the same. So, I'd like to do the sort based on the name of the field instead of the number of the field. So, instead of Field:=1, I'd like to specify Field:=[user id]. However, this gives an error that the range class failed. Here's the code: Sheet8.Range("Table_ExternalData_18").AutoFilter field:=[user id], Criteria1:=Sheet1.Range("c77").Value Any ideas on how to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help to filter data from multipule sheets to specific sheet | New Users to Excel | |||
how to set a button to filter a table | Charts and Charting in Excel | |||
unable to filter by specific data on a column if the rows exceed 1 | Excel Worksheet Functions | |||
ComboBox Filter which pulls very specific Data in Excel into preset template | Excel Programming | |||
Using Button on User Form to save Data to specific Cell?? | Excel Programming |