Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Macro- Insert rows based on dates and copy info from that row | Excel Discussion (Misc queries) | |||
sum of info in a range based on dates | Excel Worksheet Functions | |||
Macro to put info into certain columns based on info in another co | Excel Discussion (Misc queries) | |||
Sumproduct - Pulling info based on lenght of time between dates | Excel Worksheet Functions | |||
varying validation info based on preceding info | Excel Discussion (Misc queries) |