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
Bookmarks