Hello,
Does anyone know how to set up a date in a Macro so that it allows me
to put in a date when I run the Macro. When I created the macro I
created it with a criteria for a September but at the end of month I
will need to capture the data for October and so on. Is there a way to
change the dates other than manually going into the macro every month
and changing them? Listed below is the structure of the dates using
the Macro recorder:

Selection.AutoFilter Field:=37, Criteria1:="=9/5/2005",
Operator:=xlAnd _
, Criteria2:="<=10/2/2005"

How should I change the code so that it look at a new set of dates
every month?

Any help is appreciated.


--
Lizz45ie
------------------------------------------------------------------------
Lizz45ie's Profile: http://www.excelforum.com/member.php...o&userid=23410
View this thread: http://www.excelforum.com/showthread...hreadid=479917

Hello Lizz45ie,

Add 2 InputBoxes to your macro code to ask for the starting and ending
dates, like this...


Starting = InputBox("Enter Starting Date as m/d/yyyy")
Ending = InputBox("Enter Ending Date as m/d/yyyy")

Selection.AutoFilter Field:=37, Criteria1:= Starting, Operator:=xlAnd
_
, Criteria2:= Ending

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=479917

Leith,
I made the changes to the Macro and it prompts me for dates but it does
not return any data using that format. I changed the date to reflect
the date format thinking that may make different but it didn't. I know
there is data for that time frame because it worked when I hard-coded
the dates in the Macro. Do you have any idea why that isn't working?

thanks in advance.


--
Lizz45ie
------------------------------------------------------------------------
Lizz45ie's Profile: http://www.excelforum.com/member.php...o&userid=23410
View this thread: http://www.excelforum.com/showthread...hreadid=479917

Hi Lizzie,

Not sure if this is right, but shouldn't Leigh's macro include the
greater than/less than for the criteria?

Thanks

Andy

Lizz45ie wrote:
Leith,
I made the changes to the Macro and it prompts me for dates but it does
not return any data using that format. I changed the date to reflect
the date format thinking that may make different but it didn't. I know
there is data for that time frame because it worked when I hard-coded
the dates in the Macro. Do you have any idea why that isn't working?

thanks in advance.

Hello Lizz45ie,

First I want to apologize to you for having left out the comparison
operators. Secondly, thanks to Andy for catching my mistake. Thank you
for bringing it to my atttention. Again my apologies for the code error
and any inconveniece it has caused.

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=479917