Formula to get data when condition is satisfied

[Kashyap]

Hi,

Can I have formula to copy the rows that contains todays date from book1.xls
to book2.xls?

Thanks

 

[Mike H]

Hi,

A bit more detail would have helped but try this. Right click the sheet tab
where the data are, view code and paste this in and run it.

Sub copyit()
Dim MyRange, MyRange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value = Date Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Copy
Workbooks("Book2.xls").Sheets(1).Range("A1").PasteSpecial
End If
End Sub

Mike