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

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

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 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

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

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

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).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

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?