software

I need held doing three things within this macro.
3) I need to run a custom filter based on last weeks dates. ie greater
than or equal to 5-7-06 and less than or equal to 5-13-06.
1) I need the header to contain last Friday's date. ie Work report
05/12/06.
2) I need to save the file with last Friday's date. ie work report
05-12-06.xlsI only run this macro on wednesdays so I hope that based on the current
date being a wednesday there is a calculation that will let me figure
out all those dates.

Thanks!
-KThis should get you going. I suggest formating the filename with yyyymmdd so
it can be sorted by filename and still be in correct chronological order.
The below lets you run the macro anytime the following week - doesn't have
to be Wednesday.

Dim MyDay as integer

Dim LastFriday, LastMonday

Dim MyFileName as String

MyDay = Weekday(Date)

LastFriday = Date - (MyDay 1)

LastMonday = Date - (MyDay 5)

MyFileName = WorksheetFunction.Text(LastFriday, quot;yyyymmddquot;) amp; quot;.xlsquot;

db

gt; wrote in message oups.com...
gt;I need held doing three things within this macro.
gt; 3) I need to run a custom filter based on last weeks dates. ie greater
gt; than or equal to 5-7-06 and less than or equal to 5-13-06.
gt; 1) I need the header to contain last Friday's date. ie Work report
gt; 05/12/06.
gt; 2) I need to save the file with last Friday's date. ie work report
gt; 05-12-06.xls
gt;
gt;
gt; I only run this macro on wednesdays so I hope that based on the current
gt; date being a wednesday there is a calculation that will let me figure
gt; out all those dates.
gt;
gt; Thanks!
gt; -K
gt;

Kurt

Here is a macro to give you part of what you are after.
I have done it this way as sometimes I have trouble with Excel
interperating date formats different to what I expect. My normal date
format her in Australia is dd mm yy. For some reason Excel will reverse
the day amp; month. Converting date to a string has always overcome this
problem for meSub CalcFileName()
Dim sDate As String
Dim dDate As Date
Dim iWeekDay As Integer
Dim sFname As String

iWeekDay = Weekday(Now()) 1
dDate = Now() - iWeekDay
sDate$ = Format(dDate, quot;mm-dd-yyquot;)

sFname$ = quot;work report quot; amp; sDate amp; quot;.xlsquot;
End Sub--
mudraker
------------------------------------------------------------------------
mudraker's Profile: www.excelforum.com/member.php...foamp;userid=2473
View this thread: www.excelforum.com/showthread...hreadid=542867Thanks! Using this I got all but the filter piece working.
Kurt

Try this for the filter

Sub FilterData()
Dim sDates(1 To 2) As String
Dim ddates(1 To 2) As Date
Dim iWeekDay(1 To 2) As Integer

ddates(2) = Now() - Weekday(Now())
ddates(1) = ddates(2) - 8
sDates(1) = Format(ddates(1), quot;mm/dd/yyquot;)
sDates(2) = Format(ddates(2), quot;mm/dd/yyquot;)
Rows(quot;1:1quot;).AutoFilter Field:=1, Criteria1:=quot;gt;quot; amp; sDates(1), _
Operator:=xlAnd, Criteria2:=quot;lt;quot; amp; sDates(2)
End Sub--
mudraker
------------------------------------------------------------------------
mudraker's Profile: www.excelforum.com/member.php...foamp;userid=2473
View this thread: www.excelforum.com/showthread...hreadid=542867