How to set a button to filter a table using specific from and tocells data

[pilgrimm]

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

 

[Don Guillett]

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

 

[pilgrimm]

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














- Show quoted text -

Works excelent. I knew it was simple but could not figure it out.
thanks so much
Mel

 

[Don Guillett]

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

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














- Show quoted text -

Works excelent. I knew it was simple but could not figure it out.
thanks so much
Mel

 

[sonicfray]

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software







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?