run a macro for a selected date
 

I want to run a macro that extracts data from a master spreadsheet into a new
file. This is no problem.
Within the master Spreadsheet(MSS) I have columns b- ca. One column is a
date.
What I want is the extra command that will allow me run the macro that will
allow me to select only a particular date. but I want to be able to select
the date when asked.
can the macro be written so at some stage it comes up with a message what
date do you want to select? and for me to type the date in and enter for the
rest of the macro to continue

Regards

Sean...

run a macro for a selected date
 

"Sean" wrote in message
...
I want to run a macro that extracts data from a master spreadsheet into a
new
file. This is no problem.
Within the master Spreadsheet(MSS) I have columns b- ca. One column is a
date.
What I want is the extra command that will allow me run the macro that
will
allow me to select only a particular date. but I want to be able to select
the date when asked.
can the macro be written so at some stage it comes up with a message what
date do you want to select? and for me to type the date in and enter for
the
rest of the macro to continue

Yes. See Excel (VBA) Help for "InputBox Method".

run a macro for a selected date
 

PS: you'll have to be careful with the format of the input date tho' --
Excel may well see it as US-style (mm-dd-yy).

IIRC, John Green covered this somewhere -- search Google Groups for "john
green US-centric cdate".

run a macro for a selected date
 

Andy,
I need to filter the spreadsheet that over 2000 rows by one date that will
select 25 enteries
I have as part of the macro a comand that says
Selection.Autofilter
Selection.Autofilter Field:=2, Criterial:-"active"
which works great but I need to filter field 9 or column j by a selected
date, which I need to choose

"Andy Brown" wrote:

"Sean" wrote in message
...
I want to run a macro that extracts data from a master spreadsheet into a
new
file. This is no problem.
Within the master Spreadsheet(MSS) I have columns b- ca. One column is a
date.
What I want is the extra command that will allow me run the macro that
will
allow me to select only a particular date. but I want to be able to select
the date when asked.
can the macro be written so at some stage it comes up with a message what
date do you want to select? and for me to type the date in and enter for
the
rest of the macro to continue

Yes. See Excel (VBA) Help for "InputBox Method".

run a macro for a selected date
 

"Sean" wrote in message
...
Andy,
I need to filter the spreadsheet that over 2000 rows by one date that will
select 25 enteries
I have as part of the macro a comand that says
Selection.Autofilter
Selection.Autofilter Field:=2, Criterial:-"active"
which works great but I need to filter field 9 or column j by a selected
date, which I need to choose

This line will prompt for the selected date:

FilterDate = CDate(Application.InputBox("Enter filter date"))

Over here I need to use CDate -- you may not. Then you could just add
another filter line after your Field:=2 line:

Selection.AutoFilter Field:=10, Criteria1:=FilterDate

But you may need/prefer to use an If/End if to check whether any records do
actually match FilterDate in column J -- something like:

If WorksheetFunction.CountIf(Range("J1", Range("J" & Rows.Count).End(xlUp)),
FilterDate) 0 Then
Selection.AutoFilter Field:=10, Criteria1:=FilterDate
End If

HTH,
Andy

run a macro for a selected date
 

Brilliant - Thanks

"Andy Brown" wrote:

"Sean" wrote in message
...
Andy,
I need to filter the spreadsheet that over 2000 rows by one date that will
select 25 enteries
I have as part of the macro a comand that says
Selection.Autofilter
Selection.Autofilter Field:=2, Criterial:-"active"
which works great but I need to filter field 9 or column j by a selected
date, which I need to choose

This line will prompt for the selected date:

FilterDate = CDate(Application.InputBox("Enter filter date"))

Over here I need to use CDate -- you may not. Then you could just add
another filter line after your Field:=2 line:

Selection.AutoFilter Field:=10, Criteria1:=FilterDate

But you may need/prefer to use an If/End if to check whether any records do
actually match FilterDate in column J -- something like:

If WorksheetFunction.CountIf(Range("J1", Range("J" & Rows.Count).End(xlUp)),
FilterDate) 0 Then
Selection.AutoFilter Field:=10, Criteria1:=FilterDate
End If

HTH,
Andy

run a macro for a selected date
 

"Sean" wrote in message
...
Brilliant - Thanks

No problem -- thanks for the feedback. :)