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

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

    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.

  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.

    Thanks!

Leave a Reply

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