Excel Macro save as date of last friday
 

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


I 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!
-K

Excel Macro save as date of last friday
 

This 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, "yyyymmdd") & ".xls"

db

wrote in message
oups.com...
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.xls


I 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!
-K

Excel Macro save as date of last friday
 

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 & month. Converting date to a string has always overcome this
problem for me


Sub 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, "mm-dd-yy")

sFname$ = "work report " & sDate & ".xls"
End Sub


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=542867

Excel Macro save as date of last friday
 

Thanks! Using this I got all but the filter piece working.

Excel Macro save as date of last friday
 

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), "mm/dd/yy")
sDates(2) = Format(ddates(2), "mm/dd/yy")
Rows("1:1").AutoFilter Field:=1, Criteria1:="" & sDates(1), _
Operator:=xlAnd, Criteria2:="<" & sDates(2)
End Sub


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=542867