Start and end criteria macro
 

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

--
Message posted from http://www.ExcelForum.com

Start and end criteria macro
 

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

Start and end criteria macro
 

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


---
Message posted from http://www.ExcelForum.com/

Start and end criteria macro
 

Try converting the dates to serial numbers:

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


xsilverx wrote:
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.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Start and end criteria macro
 

Thank you very much Debra! It is now working fine!!

:D :D :

--
Message posted from http://www.ExcelForum.com