query info based on dates
 

I have a spreadsheet in Excel 97 with 15 columns of information, one of
which is a date entry. Is it possible to create a macro that will show a box
in which to input a date range and then to bring back information from 7 of
the other columns based on the date selection, and paste the information
into a new workbook?

Any help or guidance greatly appreciated.
Regards
Martin

query info based on dates
 

Assume column C is the date column, row 1 has headers and data starts in A2.
Sheet2 in the workbook is blank

Dim res as variant
Dim lVal as Long
Dim sh as Worksheet

res = Inputbox("Enter a date")
if not isdate(res) then
msgbox "Bad date, exiting"
exit sub
End if

lVal = clng(cdate(res))

Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:=lval
Activesheet.Autofilter.Range.Resize(,7).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
Activesheet.Autofilter
set sh = worksheets("Sheet2")
sh.copy
sh.Cells.Clear

--
Regards,
Tom Ogilvy



martin wrote in message
...
I have a spreadsheet in Excel 97 with 15 columns of information, one of
which is a date entry. Is it possible to create a macro that will show a
box
in which to input a date range and then to bring back information from 7
of
the other columns based on the date selection, and paste the information
into a new workbook?

Any help or guidance greatly appreciated.
Regards
Martin

query info based on dates
 

Thanks.


"Tom Ogilvy" wrote in message
...
Assume column C is the date column, row 1 has headers and data starts in
A2.
Sheet2 in the workbook is blank

Dim res as variant
Dim lVal as Long
Dim sh as Worksheet

res = Inputbox("Enter a date")
if not isdate(res) then
msgbox "Bad date, exiting"
exit sub
End if

lVal = clng(cdate(res))

Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:=lval
Activesheet.Autofilter.Range.Resize(,7).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
Activesheet.Autofilter
set sh = worksheets("Sheet2")
sh.copy
sh.Cells.Clear

--
Regards,
Tom Ogilvy



martin wrote in message
...
I have a spreadsheet in Excel 97 with 15 columns of information, one of
which is a date entry. Is it possible to create a macro that will show a
box
in which to input a date range and then to bring back information from 7
of
the other columns based on the date selection, and paste the information
into a new workbook?

Any help or guidance greatly appreciated.
Regards
Martin