Want a Macro for Date

[vijay]

Want a macro to sort data from (Column B,C,D,E) with whatever date mentioned
in Cell A2.
Column B,C,D,E have different dates what I want is to sort those columns as
per the date mentioned in cell A2.
Suppose if in cell A2 the date is 11/26/2009 then it should sort the data
from the Column B,C,D,E with that particular date in a new sheet in that
workbook.
And would like to assign that particular macro to a button

 

[joel]

Can you post a few rows of sample data and indicate what ROW the data
start at? I assume the data starts after row 2 since the date you are
comparing with is in cell A2.

 

[vijay]

My Data is some how like this Joel

A B C D
E
1
2 TODAY()
3 01/10/2009 15/10/2009 25/10/2009 02/12/2009
4 05/10/2009 17/10/2009 30/11/2009 12/11/2009
5 30/11/2009 02/12/2009 03/11/2009 30/11/2009
6 1/12/2009 23/10/2009 07/11/2009 01/12/2009

Now what I want is whenever there is today date in cell A2 it should sort
that for the column B,C,D & E for that particular data and given me that sort
data on a new sheet (In this case Column E has the today date that is
30/11/2009).
As the data is huge and has different dates in each column, also were in i
need to keep a track on it date wise. So can you provide me with a macro
coding.
Thanks

 

[joel]

See if this works

Sub sortDate()
Dim FindDate As String

Set Sourcesht = Sheets("sheet1")
Set DestSht = Sheets("sheet2")

NewRow = 1

With Sourcesht
FindDate = .Range("A2") 'Format(.Range("A2"), "mm/dd/yy")
RowCount = 3
Do While .Range("A" & RowCount) <> ""
'find the A2 data in current row
Set SearchRange = .Range("A" & RowCount & ":E" & RowCount)
Set c = SearchRange.Find(what:=FindDate, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
'copy date to new sheet
.Rows(RowCount).Copy _
Destination:=DestSht.Rows(NewRow)
With DestSht
Set SortRange = .Range("A" & NewRow & ":E" & NewRow)
SortRange.Sort _
Header:=xlNo, _
key1:=.Range("A" & NewRow), _
order1:=xlDescending, _
Orientation:=xlLeftToRight

NewRow = NewRow + 1
End With
End If

RowCount = RowCount + 1
Loop

End With




End Su