Announcement

Collapse
No announcement yet.

Using a Date Range with an Input Box and Selecting Data

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using a Date Range with an Input Box and Selecting Data

    Hi,

    I am trying to build a macro in excel that will enable me to do the following:

    1. Open an input box where a start date would be entered then put that date in a specified cell on a specified sheet
    2. Open an input box where an end date would be entered then put that date in a specified cell on a specified sheet
    3. Select rows of data from a large dataset that falls between and/or on these dates
    4. Copy that data to another sheet.

    The ways I can do this are quite clunky and require a lot of coding - i.e. using a series of simpler functions on the sheet and in VBA

    I was hoping that someone who has more knowledge than I would be able to help me make this a smooth process.

    Any help would be greatly appreciated.

  • #2
    Re: Using a Date Range with an Input Box and Selecting Data

    Which version of Excel are you using?

    AutoFilter would probably be the best option, but an example workbook would help you get an answer.
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: Using a Date Range with an Input Box and Selecting Data

      2007. I am not sure I can actually attach a file here...

      Esentially in Column A there are dates ranging from 2006 to the current day. I need to get a specific series of days out of that data while leaving that data undisturbed.

      As far as filters - I know how to code Filters in VBA however only with a constant (i.e. Set #3 out of 65 every time); not when the set varies each time I want to run the macro like I am trying to do. If you know of a way where I can use data residing in a cell in the workbook and use that to filter with then copy out the data then I am certainly game for it.

      Comment


      • #4
        Re: Using a Date Range with an Input Box and Selecting Data

        Here is the basic code example, no error handling included. Adjust per your needs.

        Sheet names are code names - not sheet tab names.
        Sheet2 is used to hold the filtered data and is first cleared of all previous data.

        Code:
        Option Explicit
        
        Sub Data_Date_Filter()
        
            Dim sDate As Variant, eDate As Variant
            
            sDate = Application.InputBox("Enter the starting date as mm/dd/yyyy", Type:=1 + 2)
            eDate = Application.InputBox("Enter the Ending date as mm/dd/yyyy", Type:=1 + 2)
            
            Application.ScreenUpdating = False
            
            Sheet2.Cells.ClearContents
            
            With Sheet1
                .AutoFilterMode = False
                .Range("A1").CurrentRegion.AutoFilter field:=2, Criteria1:=">=" & sDate, Operator:=xlAnd, Criteria2:="<=" & eDate
                .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Sheet2.Range("A1")
            End With
            
            Application.CutCopyMode = False
            Application.ScreenUpdating = True
        
        End Sub
        I am not sure I can actually attach a file here
        If you get an upload error due to file size limitations use ZIP compression or a file share and provide the link.
        AAE
        ----------------------------------------------------

        Forum Rules | Message to Cross Posters | How to use Tags

        Comment

        Working...
        X