You do not have to actually select either the target workbook, sheet or
cell, but it might be easier to find the right cell if you do, so...

If Not Rg Is Nothing Then
Rg.Copy
Workbooks("Year Summary").Activate
Worksheets("Summary").Activate
'Select right cell
ActiveSheet.Paste
Application.CutCopyMode = False
End If


--
Jim Rech
Excel MVP
"Victor H" wrote in message
...
| Looks like it's working except that I had to change ("A2")
| for ("A7").
|
| Also, the macro pastes the selected data onto a blank
| worksheet that it creates in the existing workbook.
| I need the data to be appended at the first blank cell
| available in a separate worksheet named "Summary"
| I know how to find the first blank cell.
|
| Should I replace "Worksheets.Add" by "Window.
| ("Summary").Activate?
| The worksheet "Summary" is in a workbook called "Year
| Summary". The worksheet is open at the time the macro is
| running.
|
| Thanks anyway for your valuable help, you saved me a lot
| of time !!!
|
|
| -----Original Message-----
| 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.
|
|
| .
|