Excel - want to copy data falling between 2 dates from one sheet to another

[Yousetouse]

Hello all.

Any help on this one (for someone who is well and truly out of his depth here) greatly appreciated.

I have played around with this one for ages and I think I am just way to much of an excel noob to even begin to understand how to do it properly.

I need to be able to somehow copy the data from a specific row of sheet 1 and paste it to sheet 2 if it the date falls in between the range I specify (eg 1 April 2012 - 30 April 2012).

Any ideas?

See attached demo

Cheers.

trevor.

[StevenM]

See: Yousetouse_demo.xlsm

Run: Run_CopyRowsBetweenDates

[Yousetouse]

StevenM,

Thanks for that mate. Much Appreciated.

How would I now extend this to copy data across to column p? (giuven that in my example sheet i only had data across to column E).

Trevor.

[StevenM]

I made two small changes.
(1) I used worksheet objects, so that you can change the name of the worksheets from Sheet1 and Sheet2 if you need to.

So you only need to change the items in the quotes to change the sheet names (if ever needed).

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

(2) I change "E" to "P" (as requested).
Anytime you see "P" inside quotation marks, that is the last column being used.

Sub CopyRowsBetweenDates(dtStart As Date, dtEnd As Date)
    Dim nRow As Long, nLastRow As Long, nNextRow As Long
    Dim dt As Date
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    
    With ws2
        nLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        If nLastRow < 3 Then
            ws1.Range("B3:P3").Copy
            .Range("B3").PasteSpecial
        End If
        nNextRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
    End With
    
    With ws1
        nLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        For nRow = 4 To nLastRow
            dt = .Cells(nRow, "B")
            If dt >= dtStart _
            And dt <= dtEnd Then
                .Range(.Cells(nRow, "B"), .Cells(nRow, "P")).Copy
                ws2.Range("B" & nNextRow).PasteSpecial
                nNextRow = nNextRow + 1
            End If
        Next nRow
    End With
    ws2.Range("B3:P" & nNextRow - 1).Columns.AutoFit
End Sub

Sub Run_CopyRowsBetweenDates()
    CopyRowsBetweenDates #4/1/2012#, #4/30/2012#
End Sub

P.S. To change the dates, change:

Sub Run_CopyRowsBetweenDates()
    CopyRowsBetweenDates #4/1/2012#, #4/30/2012#
End Sub