Macro Saves & Names File with Current Date & Range

Hi, all,

This is sort of related the post below but with a slight twist.
http://www.excelforum.com/showthread...highlight=date

I would like to create a macro that saves and names a file with the current date and the day after. The code below works, but is somewhat limited.

Sub SaveFileWithDate()

MyMonth = Left(MonthName(Month(Date)), 3)
MyDay = Day(Date)
MyDay1 = Day(Date + 1)

MyFileName = "Schedule " & MyMonth & " " & MyDay & " - " & MyMonth & " " & MyDay1 & ".xls"

ActiveWorkbook.SaveAs Filename:="C:\Admin" & MyFileName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
        
ActiveWindow.Close

Workbooks.Open "C:\Admin" & MyFileName
   
End Sub

What I want is for the macro to only input weekdays. In other words if I run the macro on Fri. 4/11, I want it to name the file "Schedule April 11 - April 14." In this case 4/14 is a Mon. So the macro skips the weekend dates.

Is there anyway for this to work? Any help would be greatly appreciated.

Will you always only be creating this file Mon-Fri? If so, you can define your variables as follows:

MyMonth = Format(Date, "mmm")
MyDay = Day(Date)
If Weekday(Date + 1, vbMonday) > 5 Then
    MyDay1 = Day(Date + 3)
Else
    MyDay1 = Day(Date + 1)
End If

HTH

Jason

Yes. This particular report will only be created with a Mon-Fri date.

I tried the code you provided, and it totally worked. It was exactly what I needed. I was even able to use it as a template for another macro I was using. It fixed a similar problem there, too.

Thanks a lot!

Glad it helped.

Jason

Hi,

It's me again. So the code that jasoncw provided continues to work great, but I have noticed when it comes to the end of the month, the file is renamed with the same month.

So if I were to save it today 5/30 the file would be saved as "Schedule May 30 - May 2.xls" How can I get it to say "Schedule May 30 - June 2.xls" Basically at the end of the month, I would like it to get renamed with the following month included.

This is what I have so far.

Sub SaveFileWithDate()

MyMonth = Format(Date, "mmm")
MyDay = Day(Date)

If Weekday(Date + 1, vbMonday) > 5 Then
    MyDay1 = Day(Date + 3)
Else
    MyDay1 = Day(Date + 1)
End If


MyFileName = "Schedule " & MyMonth & " " & MyDay & " - " & MyMonth & " " & MyDay1 & ".xls"

ActiveWorkbook.SaveAs Filename:="C:\Admin" & MyFileName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
        
ActiveWindow.Close

Workbooks.Open "C:\Admin" & MyFileName
   
End Sub

Thanks!

See the added IF statement and new variable (MyMonth1) in red below that should correct this issue:

Sub SaveFileWithDate()

MyMonth = Format(Date, "mmm")
MyDay = Day(Date)

If Weekday(Date + 1, vbMonday) > 5 Then
    MyMonth1 = Format(Date + 3, "mmm")
Else
    MyMonth1 = Format(Date + 1, "mmm")
End If

If Weekday(Date + 1, vbMonday) > 5 Then
    MyDay1 = Day(Date + 3)
Else
    MyDay1 = Day(Date + 1)
End If


MyFileName = "Schedule " & MyMonth & " " & MyDay & " - " & MyMonth1 & " " & MyDay1 & ".xls"

ActiveWorkbook.SaveAs Filename:="C:\Admin" & MyFileName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
        
ActiveWindow.Close

Workbooks.Open "C:\Admin" & MyFileName
   
End Sub

HTH

Jason