query info based on dates

  • Thread starter Thread starter martin
  • Start date Start date
M

martin

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
 
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
 
Thanks.


Tom Ogilvy said:
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
Click to expand...
 
Back
Top