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
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
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:
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.
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!
Dear sir
why I can’t copy more than two columns to the report sheet as per your code?
Hi Sir can you please provide the source file. because in lastrow show runtime error 424.
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
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"
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
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
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