How to copy special rows from one sheet to another?

[wwoman]

Hello,

I have an excel spreadsheet which has many rows within it..

I have created a conditional format which compares the date field in these rows to the current date. If the date in the row is equal to the current date, then that cell is displayed in red.

SO far so good..

What I want to do now is to copy ALL these ROWS which have been displayed as red into another sheet.

Can someone please tell me how I can do that?

Thanks,
Asha

 

[TonyJollans]

Hi wwoman,

I would use Advanced Filter for this.

Assuming your dates are in column A, and your column headings are in row 1 on Sheet1 ..

In Sheet2, A1, put [blue][tt]=Sheet1!A1[/tt][/blue]
In Sheet2, A2, put [blue][tt]=TODAY()[/tt][/blue]

Run this:

[blue]Sheets("Sheet1").Range("A:F").AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Sheet2").Range("A1:A2"), _
    CopyToRange:=Sheets("Sheet2").Range("A3")[/blue]

This will copy your data (columns A to F in this example) to Sheet2 starting in row 3. Adjust the references to suit.


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.