Dan,

Try this and change sheets etc as needed :

Sub Compare_Dates()

Dim lastrow As Long, r1 As Long, r2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
r2 = 2

With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row

For r1 = 2 To lastrow
If Format(.Cells(r1, 1), "MMYY") = Format(Now(), "MMYY") Then
.Cells(r1, 1).Offset(0, 5).Copy ws2.Cells(r2, 1) '<=== change as
required
r2 = r2 + 1
End If
Next r1
End With
End Sub


"Mr. Dan" wrote:

Hello,

I would like to run a sub everytime a worksheet is opened that compares
todays date with a series of dates listed in column A. Cell A1 would, for
example, have May-05 (5/1/2005), cell A2 would have Jun-05 (6/1/2005), etc.

If todays month and year match up to a cell in the A column, then a value
from that row (maybe 5 cells over) is pasted into another worksheet.

Been playing around with this for a while and just can't get it!

Thanks in advance,
Dan