select data based on user input

D

Dave Ramage

If the dates are sorted, then this will work:

Sub GetData()
'assuming sorted by dates ascending
Dim strTemp As String
Dim dtStart As Date, dtEnd As Date
Dim lR1 As Long, lR2 As Long
Dim wsW As Worksheet

Do 'get start date
strTemp = Application.InputBox("Enter start
date:", Type:=2)
If strTemp = "False" Then Exit Sub
Loop While Not IsDate(strTemp)
dtStart = CDate(strTemp)

Do 'get end date
strTemp = Application.InputBox("Enter end date:",
Type:=2)
If strTemp = "False" Then Exit Sub
Loop While Not IsDate(strTemp)
dtEnd = CDate(strTemp)
'find largest date <= dtStart
lR1 = Application.Match(CLng(dtStart), Sheets
("Sheet1").Range("A:A"), 1)
'add one line if necessary
If Sheets("Sheet1").Cells(lR1, 1).Value <> dtStart
Then lR1 = lR1 + 1
lR2 = Application.Match(CLng(dtEnd), Sheets
("Sheet1").Range("A:A"), 1)

Set wsW = ActiveWorkbook.Worksheets.Add
Sheets("Sheet1").Range("A" & lR1 & ":B" & lR2).Copy
Destination:=wsW.Cells(1)
End Sub

Cheers,
Dave.
 
Top