VBA to rename file to last sunday to saturday

[Knockoutpie]

Hi everyone, i've had luck getting VBA to rename a file based on the current date.
However, every Monday I work on a file that's dated from Last Sunday to Last Saturday.

When I rename this file I name it "POS 20190120-20190126"

YY-MM-DD

Can anyone help me have VBA automatically name/save the file to last weeks Sunday-Saturday? I'd appreciate any tips.

 

[JLGWhiz]

Sub t()
Dim fName As String
fName = "POS " & Format(Date - 8, "yyyymmdd") & "-" & Format(Date - 2, "yyyymmdd")
MsgBox fName
End Sub

 

[Knockoutpie]

Sub t()
Dim fName As String
fName = "POS " & Format(Date - 8, "yyyymmdd") & "-" & Format(Date - 2, "yyyymmdd")
MsgBox fName
End Sub

Thanks! Works great, i used a little help from Chip also!

https://answers.microsoft.com/en-us...e-a-file/e85bea4e-1e82-e011-9b4b-68b599b31bf5

Sub (macro)
'


'


Dim fName As String
Dim FPath As String
fName = "POS " & Format(Date - 8, "yyyymmdd") & "-" & Format(Date - 2, "yyyymmdd")
MsgBox fName


FPath = "C:\Users\paul.ferguson\Downloads"


ThisWorkbook.SaveAs Filename:=FPath & "" & fName & ".xlsm", _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled
'
End Sub

 

[Knockoutpie]

[COLOR=#333333]Sub (macro)[/COLOR]'


'


Dim fName As String
Dim FPath As String
fName = "POS " & Format(Date - 8, "yyyymmdd") & "-" & Format(Date - 2, "yyyymmdd")
MsgBox fName


FPath = "C:\Users\paul.ferguson\Downloads"


ActiveWorkbook.SaveAs Filename:=FPath & "" & fName & ".xlsm", _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled
' [COLOR=#333333]End Sub[/COLOR]

I had to change to ActiveWorkbook to open files.

Thanks for your help!

 

[JLGWhiz]

You're welcome,
regards, JLG

 

[Knockoutpie]

Currently what I have works great, and I can continue to use it. However on the occasion that I take a Monday off and return tuesday, the dates are off because the report was not run on Monday. Any way to fix this?

Dim fName As String
Dim FPath As String
fName = "POS " & Format(Date - 8, "yyyymmdd") & "-" & Format(Date - 2, "yyyymmdd")
MsgBox fName

 

[JLGWhiz]

You can try this and see if does what you want.

fName = "POS " & Format(Date - (7 + Weekday(Date, vbSunday)), "yyyymmdd") & "-" & Format(Date - 2, "yyyymmdd")