Closed Thread
Results 1 to 5 of 5

Excel Macro save as date of last friday

  1. #1

    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


  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    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

  3. #3
    Dave
    Guest

    Re: 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

    <[email protected]> wrote in message
    news:[email protected]...
    >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
    >




  4. #4

    Re: Excel Macro save as date of last friday

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


  5. #5
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    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

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1