Saving Filename as Week Number and Date on Friday

[Squreshi]

Hi, I need to save a file (every week) in excel vba code with a file name which looks like this: "book1.W23.Dec15.xlsx". This file is saved during the week on any day of the week. Here the Week number needs to be a fiscal week number where the year starts on July 1 and ends on June 30th and then "Dec15" will be the date on each Friday of that particular week. The steps as I can figure out will be to see the current date with Now(), then extract the corresponding week number and then the date on Friday but I keep on messing this up. Also I cannot seem to figure out how can I set the year to start from July 1 from the default Jan 1. Can you please help. Thanks

 

[Squreshi]

Apologies, forgot to mention that my week starts on Sunday, being in middle east and I am using excel 2013. Thanks

 

[Squreshi]

Okay so this is the code that I came up after going through various resources on web. I just need a senior member to do a helpful review of this code in sense if i am missing someting. Also the intention here is to save in the same directory where the active workbook was opened from. Thanks.

'Saving the file in the same directory with week and date


Dim WeekNumber As Integer
Dim LastFriday As Date
Dim NameToSave As String




WeekNumber = Int((((Now() - DateSerial(2017, 7, 2) + 6) / 7) + Abs(Weekday(Now()) = Weekday(DateSerial(2017, 7, 2)))) - 1)
LastFriday = Int(Now() - Weekday(Now()) - 1)
NameToSave = "WIP.W" & WeekNumber & "." & Format(LastFriday, "mmmdd") & ".xlsx"


ActiveWorkbook.SaveAs Filename:=NameToSave, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False