Start and end criteria macro

  • Thread starter Thread starter xsilverx
  • Start date Start date
X

xsilverx

Does anyone know how to create a macro that when specific criteria i
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 fro
the sheet to copy to a report. I would enter the start date and the
the end date. The end date must be last end date if there a tw
enteries for the same date. the info would then be selected for cop
and paste.

Regards
Pau
 
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
'==========================================
 
Thank you for explainging the above.

I have adapted part of the suggested VBA for my project and only use
the part:

Dim strStart As String
Dim strEnd As String
strStart = Application.InputBox("Start Date, (dd/mm/yyyy)")
strEnd = Application.InputBox("End Date, (dd/mm/yyyy)")

Selection.AutoFilter Field:=1, _
Criteria1:=">=" & CDate(strStart), Operator:=xlAnd, _
Criteria2:="<=" & CDate(strEnd)

It all works ok but after the filter there is no displayed data. I have
tried the record macro function and adapted in parts for input but this
does not resolve the problem.

This problem does not occur when the custom filter is used manually.

I do require the macro as the spreadsheet is used by a number of people
with different levels of IT knowlege.

Regards
Paul
 
Try converting the dates to serial numbers:

Selection.AutoFilter Field:=1, _
Criteria1:=">=" & CLng(CDate(strStart)), Operator:=xlAnd, _
Criteria2:="<=" & CLng(CDate(strEnd))
 
Back
Top