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
Sheet1.Range(“A1”).Select
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

2 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,,,

    http://www.exceltrainingvideos.com/extract-data-from-sheet1-to-sheet2-based-on-date-criteria/

    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.

    Reply
  2. Henri

    Hi,
    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.

    Thanks!

    Reply

Leave a Reply

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