Consulting

Results 1 to 10 of 10

Thread: vba excel macro data between two dates

  1. #1

    vba excel macro data between two dates

    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

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Mod bump
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Nov 2016
    Posts
    13
    Location
    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
    Attached Files Attached Files

  4. #4
    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.

  5. #5
    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?

  6. #6
    Hi Senor,

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

  7. #7
    Quote Originally Posted by Rajhesh View Post
    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.
    Last edited by SenorHazz; 05-03-2019 at 06:59 AM.

  8. #8
    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..

  9. #9
    VBAX Newbie
    Joined
    May 2020
    Posts
    3
    Location
    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???

  10. #10
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    This Thread is very old. Please start a new thread.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •