Short VBA Code Help

[pomereng]

I have a list of dates in column M that I already have sorted from earliest date on. I'm trying to create a macro that will look from M2 to the last cell used in column M and If it finds the specific date/s, to copy the row, then offset it below the data by 2 rows, and paste the row.

This is what I have so far, obviously it's not working as I have never used "For Each" loops or If-Then Codes before.

Sub DateRng()
'
' DateRng Macro
'


'
Dim oscar As Date


oscar = CDate(mm / dd / yyyy)


For Each oscar In Sheets(D186).Range("M2", & Rows.Count)
If oscar.Date = "11/23/2009" Then
ActiveRow.Select
Selection.Copy

Range("A1").End(xlDown).Offset(2, 0).Select
ActiveSheet.Paste
End If
Next


End Sub

 

[JackDanIce]

This may work, but I'm not sure as I do not know the date format your worksheet is using. Also, is your sheet called D186? If so, it should be inside speach-marks, i.e. Sheets("D186")

Sub DateRng()

' DateRng Macro

Dim rng As Range

Application.ScreenUpdating = False

For Each rng In Sheets(D186).Range("M2:M" & Rows.Count)
    If rng.Value = "11/23/2009" Then
        rng.EntireRow.Copy
        Range("A" & Rows.Count).End(xlUp).Offset(2).Paste
    End If
Next rng

With Application
    .ScreenUpdating = True
    .CutCopyMode = False
End With

End Sub

 

[pomereng]

Jack,

Yes the sheet is "D186" and the date is just the general Short Date format