December 24, 2015

Extract Data from Sheet1 to Sheet2 based on Date Criteria

Posted on by Dinesh Kumar Takyar

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

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

    https://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!

  4. Hi Sir can you please provide the source file. because in lastrow show runtime error 424.

  5. Hi sir.

    I show you video and the knowledge you provide to others very much appreciated. Sir I am facing small issue in my data sheet. I have multiple rows and columnand sheets in my raw data and I want to copy data which will match system date using vba please can you help me thanks

  6. 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)

    In the above code, I want to input the cell number of the paticular sheet instead of "14-mar-2012"

  7. Hi

    I am looking for a VBA to open a workbook (FX rate log) from a specific file path and the look for a specific date (Today()-1) and copy the rows including the date and various currency rates to another workbook (Working model) within worksheet (FX Rate).

    Data layout in FX Rate log – (Worksheet “Sheet 1)

    Date AUD CAD USD ZAR
    05/06/2019 0.9456 0.7665 0.7789 5.4321
    06/06/2019 0.9457 0.7890 0.7898 5.6543
    07/06/2019 0.9467 0.7564 0.6987 5.3215

    Working Model – (Worksheet “FX Rate”)

    Date AUD CAD USD ZAR
    05/06/2019 0.9456 0.7665 0.7789 5.4321
    06/06/2019 0.9457 0.7890 0.7898 5.6543

    I would like to get the entire row of 07/06/2019 from FX rate log without the headings under the working sheet . In the working model workbook I have the date 07/06/2019 in cell “B2” of sheet 1 which I change daily for reporting. So if the macro could use that date as a reference it would be even better so I can ignore Today()-1

    Thanks

    Roopak

  8. Excellente!
    Can you help me create a macro that copies values ​​to a tab based on date? Example: In a record sheet we have values ​​from January to December by date. Depending on the registration date, the macro should copy January values ​​to January tab, February values ​​to February tab, etc.Thanks

  9. i am getting object required error

    Sub datewise_extraction()
    Dim lastrow As Long
    Dim erow As Long
    Dim i As Integer
    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, 1)
    If mydate = “16-10-2021 06:35:50” Then
    erow = Sheet9.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Range(Cells(i, 1), Cells(i, 2)).copy Destination:=Sheets(“Sheet9”).Cells(erow, 1)
    End If
    Next i

    End Sub

Comments are closed.