vba excel macro data between two dates

[ashu123]

Hi all,
I am beginner in vba excel i have data in sheet3 and user giving two dates from sheet1 i want vba code which will find data between those two selected dates in sheet 3 my first column is date column.can anybody help me
Thanks in advance

[SamT]

Mod bump

[KevO]

Try this
Amend Start and End dates in cells A4 and B4 (sheet1) respectively and click where indicated
Filtered values are copied and then pasted to sheet4





Sub SelectDataBetweenTwoDates()
'declare variables
Dim fromDate, toDate
Dim MyResults As Worksheet, MyData As Worksheet, MyDates As Worksheet

Set MyResults = Worksheets("Sheet4")
Set MyData = Worksheets("Sheet3")
Set MyDates = Worksheets("Sheet1")

'clear previous results
MyResults.Cells.Clear

'attribute date values to variables
fromDate = MyDates.Range("A4").Value
toDate = MyDates.Range("B4").Value

'convert to text format to allow filtering
fromDate = Format(fromDate, "dd-mmm-yyyy")
toDate = Format(toDate, "dd-mmm-yyyy")

With MyData
'removes autofilter
If .FilterMode Then .ShowAllData
'filter the data based on selected date values
.Range("$A$1:$C$246").AutoFilter Field:=1, Criteria1:= _
">=" & fromDate, Operator:=xlAnd, Criteria2:="<=" & toDate
'copy the filtered data
.UsedRange.SpecialCells(xlCellTypeVisible).Copy
'paste copied values to results sheet
MyResults.Range("A1").PasteSpecial
End With
'select cell A1 in results sheet
MyResults.Activate
MyResults.Range("A1").Select

End Sub

EDIT

Apologies, just spotted that the range for filtering was the specific one used in my test data. To include all rows amend the range in the code as follows:

amend

.Range("$A$1:$C$246").AutoFilter Field:=1, Criteria1:= _
">=" & fromDate, Operator:=xlAnd, Criteria2:="<=" & toDate
to

.Range("A:C").AutoFilter Field:=1, Criteria1:= _
">=" & fromDate, Operator:=xlAnd, Criteria2:="<=" & toDate

[ramola0911]

I have downloaded attached file and run successfully, but i want to add one more criteria i.e. product=apple then run the vba code. How can it possible.

[SenorHazz]

I have a situation where I have used allot of your code above however, I have a number of errors:
When converting the text format to allow filtering, an error message comes up stating 'Compile Error: Wrong number of arguments or invalid property assignment'. I have removed this and tested the macro without it however, the macro applies a filter but, no data is shown. I go into the filter (between dates) and reapply the data by simply clicking ok, and it then filters. Is there anyway to solve this?

[Rajhesh]

Hi Senor,

I'm also facing the same issue. Kindly let me know, if you got any solution in the same thread. Thanks.

[SenorHazz]

Hi Senor,

I'm also facing the same issue. Kindly let me know, if you got any solution in the same thread. Thanks.


Hi Rajhesh, I am using the following code and works a treat!


Range("O1").Select 'This is your First filtered date
Range("O1").NumberFormat = "mm/dd/yyyy"
Date1 = ActiveCell.Text




Range("P1").Select 'This is your First filtered date
Range("P1").NumberFormat = "mm/dd/yyyy"
Date2 = ActiveCell.Text


Rows("1:1").Select
Selection.AutoFilter
Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & Date1, Operator:=xlAnd, Criteria2:="<=" & Date2
'Field 1 is whichever column that your dates are in


Hope this is of some help.

[Rajhesh]

Hard luck..I'm missing something. Here is my code

dte = Sheets("Tickets_Created").Range("D3").Value //29-04-2019 00:24 //
ST = Format (dte, "dd-mm-yyyy") + " 03:00" // I'm picking the date from Excel data in order to be dynamic selection
SE = Format (dte, "dd-mm-yyyy") + " 10:00" // 3 am - 10 am is the shift, for which I need to filter the data, from 24 hrs.

Range("D1").CurrentRegion.Sort key1:=Range("D1"), order1:=xlAscending, Header:=xlYes
Cells.Select
ActiveSheet.Range("A:L").AutoFilter Field:=4, Criteria1:=">=" & ST, Operator:=xlAnd, Criteria2:="<=" & SE, Operator:=xlFilterValues

Like said in the above thread, it applies the date filters in between, but not selecting it by code. When I manually click "OK" in the between filter, data gets filtered. I tried your suggestion, but couldn't get the result.

Any alternatives or suggestions..:(

[maithriy]

Yeah it was gud, but this is particularly working if the data was available in single file. If my Database file was separate then How can i get my data???

[SamT]

This Thread is very old. Please start a new thread.