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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

3 thoughts on “Extract Data from Sheet1 to Sheet2 based on Date Criteria”

  1. 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. 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.


Leave a Reply

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