This should do the copy. The matching rows do not have to be consecutive.

Sub a()
Dim Cell As Range
Dim Rg As Range
For Each Cell In Range(Range("a2"), Range("A65536").End(xlUp))
If Cell.Value2 = CLng(Date) - 1 Then
If Rg Is Nothing Then
Set Rg = Cell.Resize(1, 14)
Else
Set Rg = Union(Rg, Cell.Resize(1, 14))
End If
End If
Next
If Not Rg Is Nothing Then
Rg.Copy
Worksheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
End If
End Sub


--
Jim Rech
Excel MVP
"Victor H" wrote in message
...
|I have a worksheet with rows covering 14 columns from (Axx
| to Nxx).
| The first row contains a date. The other rows contain
| miscellaneous other data.
|
| Out of all the rows already in the worksheet, I need to
| select those rows (they are consecutive) that satisfy the
| following criteria:
|
| Select row if Axx = Today()-1 (Axx is already formatted as
| a Date)
|
| In other words, I'd like to select all rows with
| yesterday's date and paste them to another worksheet.
|
| I've already written a macro that does that, but I have to
| select the rows myself before the macro copies and pastes.
| That macro would look a lot better if it could work
| unattended.
|
| Thanks in advance for any help.