Results 1 to 4 of 4

How do you input user defined variables into an AutoFilter statement?

Threaded View

trhoades How do you input user defined... 07-14-2010, 10:56 PM
Leith Ross Re: Need help inputting user... 07-14-2010, 11:11 PM
trhoades Re: How do you input user... 07-14-2010, 11:23 PM
trhoades Re: How do you input user... 07-14-2010, 11:26 PM
  1. #1
    Registered User
    Join Date
    07-03-2010
    Location
    San Antonio Texas
    MS-Off Ver
    Excel 2007
    Posts
    3

    How do you input user defined variables into an AutoFilter statement?

    I am trying to create a macro which allows a user to input a begining date and ending date which in then input into the macro to select rows for coping and pasting to a new sheet.

    I think I'm close with the following code. The problem is I do not know how to call the Variables into the Criteria for the AutoFilter.
    Sub selectAndcopy2()
    '
    ' selectAndcopy2 Macro
    '
    
    '
    
        Dim MyInput1 As Date
            MyInput1 = InputBox("This is the Beginning Date and must be input in mm/dd/yyyy format", _
            "Beginning Date", Format(Date, "mm/dd/yyyy"))
    
        MsgBox "The Beginning date is " & MyInput1
        
        ActiveSheet.Range("Y1").Value = MyInput1
    
        Dim MyInput2 As Date
            MyInput2 = InputBox("This is the Ending Date and must be input in mm/dd/yyyy format", _
            "Ending Date", Format(Date, "mm/dd/yyyy"))
    
        MsgBox "The Ending Date is " & MyInput2
    
        ActiveSheet.Range("Z1").Value = MyInput2
            
        ActiveSheet.Range("$A$1:$F$45").AutoFilter Field:=1, Criteria1:= _
            ">= MyInput1", Operator:=xlAnd, Criteria2:="<= MyInput2"
        Range("A1:C6").Select
        Selection.Copy
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Paste
    End Sub



    Any help would be much appreciated.

    Tony
    Last edited by trhoades; 07-14-2010 at 11:11 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1