Copying yesterday's data.

[Byrdwatcher]

Hi,

I'm quite new to VBA and currently face the following. I have an excel workbook with many sheets grouped by month.

On Tuesday - Friday, i need to copy the value from column C for the previous day. On Monday, I need to copy Friday's value from Column C.
I then need to paste these values in another workbook.

Columns are listed like this.
Date(Column A) X(Column B) Y(Column C)

I've made a stab at the copy part but as of yet have only got to copying yesterday's date from Column A.

So, I need to
1. Search the workbook, not just the worksheet for yesterday's C value from Tuesday to Friday. And Friday's value on Monday.
2. Copy the corresponding data from the C Column and paste it to another Workbook, Test.xls

Thanks for any help.

Sub CopyRow()
Dim Rng As Range
Dim i As Range

With Worksheets("March_2011")
Set Rng = Range(.Range("A4"), .Range("A38").End(xlDown))

For Each i In Rng
If i = Date - 1 Then
i.Copy

End If
Next i
End With

End Sub