Copy Data, Find Date, Paste data below Date

So I am looking to write a simple script to use today's Date, then on sheet1 copy a set range of cells (eg: C1:C5) then go to sheet 2, Find today's date that is already entered on that sheet and paste the data below the date.

So If on Sheet2 today's date is in cell B1, paste the data from Sheet1 into cells B2 and below.

Simple? Questions? I'm completely flustered so any help would be super nice!

Thanks in advance everyone!
-Dave

Hi Dave,

Maybe something like...

Sub Test()
    Dim Found As Range
    Dim ans As String
    Set Found = Sheets("Sheet2").Columns("B").Find(What:=Date, LookIn:=xlValues, LookAt:=xlWhole)
    If Not Found Is Nothing Then
        Sheets("Sheet1").Range("C1:C5").Copy Sheets("Sheet2").Range("B" & Found.Row).Offset(1, 0)
    End If
End Sub

Dates are very tricky in VBA and quite honestly it seems I have not mastered them as of yet, but here is what I have.

On the FSR_Metrics sheet in row 2 is where I am searching. You have to make sure the date is in the same format as Date in VBA returns.

Date returns 2/10/2011 so if you make the cell match it will run. Just change the format

If you can't get it to work I will upload the sample sheet.

Sub FindToday()
    Dim Found As Range
    Dim rngSrc As Worksheet
    Dim rngDst As Worksheet
    Set rngSrc = ActiveWorkbook.Sheets("Regression_Report")
    Set rngDst = ActiveWorkbook.Sheets("FSR_Metrics")
    Set Found = rngDst.Rows(2).Find(what:=Date, LookIn:=xlValues, lookat:=xlWhole)

    If Found Is Nothing Then Exit Sub
        rngSrc.Range("B2:B6").Copy
        rngDst.Cells(Found.Row, Found.Column).Offset(1, 0).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    
    Set rngSrc = Nothing
    Set rngDst = Nothing
    Set Found = Nothing
End Sub

Edit: If you want to keep the same format you can update to

Set Found = rngDst.Rows(2).Find(what:=Format(Date, "dd-mmm"), LookIn:=xlValues, lookat:=xlWhole)

You're welcome...sure glad you got it to work for you.