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

3 thoughts 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.

  2. Henri

    have 2 questions:
    1) How to search by date? on textbox. Like from 03/25/17 need to see data only on the user form.
    2) how to Disable a textbox after I enter a number or name? Like ID is unique number. this textbox need to get disable after any value is in.



Leave a Reply

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