Extract Data from Sheet1 to Sheet2 based on Date Criteria

How to extract data from Sheet1 to Sheet2 based on Date criteria with VBA in Microsoft Excel. We use a looping process and check for dates in a range like this:

If mydate >= “14-mar-2012” And mydate <= “20-mar-2012”

Watch the training video below and then study the VBA macro code step by step:

Watch the video on YouTube.

Complete VBA macro code to copy data from sheet1 to sheet2 with VBA using dates as criteria:

Sub extractDataBasedOnDate()
Dim lastrow As Long, erow As Long, i As Long
Dim mydate As Date
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
    mydate = Cells(i, 2)
If mydate >= “14-mar-2012” And mydate <= “20-mar-2012” Then
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Range(Cells(i, 1), Cells(i, 2)).Copy Destination:=Sheets(“sheet2”).Cells(erow, 1)
End If
Next I
End Sub
Further reading:

How to use dates and time in Excel

One thought on “Extract Data from Sheet1 to Sheet2 based on Date Criteria

  1. Thomas

    Thank you for publishing this. Very useful!
    Just for information, I copied and pasted the text from you site,,,


    At first it would not work, however the solution was to replace the quotation marks (“A1”) with my quotation marks on my keyboard, (“A1”) I noticed yours appeared to be leaning to the right. That seemed to fix the problem.

    Once again, Thank you.


Leave a Reply

Your email address will not be published. Required fields are marked *