You can use an AutoFilter to filter for data in a date range, then copy
the visible cells to a different sheet. The following macro was adapted
from code posted by Tom Ogilvy:
http://www.contextures.com/xlautofilter03.html#Copy
'=====================================
Sub CopyDateRange()
Dim rng As Range
Dim rng2 As Range
Dim strStart As String
Dim strEnd As String
strStart = Application.InputBox("Start Date")
strEnd = Application.InputBox("End Date")
Selection.AutoFilter Field:=1, _
Criteria1:="=" & CDate(strStart), Operator:=xlAnd, _
Criteria2:="<=" & CDate(strEnd)
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No dates in that range"
Else
Worksheets("Sheet2").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Copy Destination:=Worksheets("Sheet2").Range("A1")
End If
ActiveSheet.ShowAllData
End Sub
'==========================================
xsilverx wrote:
Does anyone know how to create a macro that when specific criteria is
input to input boxes it searches and copys data between the two.
i.e. if i had data filed by dates and i wanted to pull information from
the sheet to copy to a report. I would enter the start date and then
the end date. The end date must be last end date if there a two
enteries for the same date. the info would then be selected for copy
and paste.
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html