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