Saving file with previous Monday date in filename

[Darren_workforce]

Hello,

My macro file generates a new worksheet and at the end, it saves the file to my work desktop. I am currently renaming the file and adding the date in manually. I'd love to incorporate the prior Monday's date as well if that's possible just to shave off a few steps before I need to send the data to my superiors.

Today's date is 6.6.2023 and last Monday was 5.29. If I were to run the file today, preferably the filename would be "Attendance Report week of 5.29.2023". Below is what I have setup now. And I won't always run the report on a Tuesday so regardless of the weekday I run it, the results should still be the previous Monday.

All help would be greatly appreciated.

ActiveWorkbook.SaveAs Filename:="C:\Users\darren\desktop" & fName & "\Attendance Report week of " & ".xlsx" _
, FileFormat:=xlWorkbookDefault, CreateBackup:=False

 

[ranman256]

how bout this:

dim vDat as date
vDat = getLastMondaydate(date())
vDateName = format(vDat,"mm.dd.yyyy")
vFile = "C:\Users\darren\desktop" & fName & "\Attendance Report week of " & vDateName & ".xlsx"
ActiveWorkbook.SaveAs Filename:= vFile

Public Function getLastMonday(ByVal pvDate)
Dim iDow As Integer, iSTARTDAY As Integer
Dim i As Integer
Dim vStart, vEnd

iSTARTDAY = vbMonday

iDow = Format(pvDate, "w")
Select Case True
   Case IsNull(pvDate)
     'nothing to do
  Case Else
        vStart = DateAdd("d", -(iDow - 1), pvDate)
        vEnd = DateAdd("d", 6, vStart)
End Select

getLastMonday = DateAdd("d", -6, vStart)
End Function

 

[Darren_workforce]

how bout this:

dim vDat as date
vDat = getLastMondaydate(date())
vDateName = format(vDat,"mm.dd.yyyy")
vFile = "C:\Users\darren\desktop" & fName & "\Attendance Report week of " & vDateName & ".xlsx"
ActiveWorkbook.SaveAs Filename:= vFile

Public Function getLastMonday(ByVal pvDate)
Dim iDow As Integer, iSTARTDAY As Integer
Dim i As Integer
Dim vStart, vEnd

iSTARTDAY = vbMonday

iDow = Format(pvDate, "w")
Select Case True
   Case IsNull(pvDate)
     'nothing to do
  Case Else
        vStart = DateAdd("d", -(iDow - 1), pvDate)
        vEnd = DateAdd("d", 6, vStart)
End Select

getLastMonday = DateAdd("d", -6, vStart)
End Function

I'm trying to add that into my sub and am being told "Sub or Function not defined". Maybe I should post the full sub just in case I'm not including your data correctly (which is highly likely):

Sub New_Book()
'
   Sheets(Array("Supe Breakdown", "% Breakdown", "Chart", "Raw Data", "% Raw Data")).Copy
   
Worksheets(3).Select

ActiveWorkbook.SaveAs Filename:="C:\Users\darren\desktop" & fName & "\Attendance Report week of" & ".xlsx" _
, FileFormat:=xlWorkbookDefault, CreateBackup:=False
   
End Sub