Following is my code, basically this report is run weekly from previous
Monday date to Sunday date. This macro does everything except add in those
dates to the file name. Any suggestions?

Dim wb As Workbook
Dim ws As Worksheet
Dim x As Variant
Dim y As Variant
Dim strFileName As String
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("idrmterm")
x = Format(Date - 7, mm - dd - yy)
y = Format(Date - 1, mm - dd - yy)
Dim strPath As String
strPath = "H:\REPORTS\Every Monday Reports\"
'===strFileName = x & " " & y & ws & ".xls"<<<===
ActiveWorkbook.SaveAs Filename:=strPath & strFileName
Set wb = Nothing
Set ws = Nothing
Application.Dialogs(xlDialogSendMail).Show
ActiveWorkbook.Close
End Sub

strPath = "H:\REPORTS\Every Monday Reports\"
strFileName = x & " " & y & ws.Name & ".xls"

--
Regards,
Tom Ogilvy

"CV323" wrote:

Following is my code, basically this report is run weekly from previous
Monday date to Sunday date. This macro does everything except add in those
dates to the file name. Any suggestions?

Dim wb As Workbook
Dim ws As Worksheet
Dim x As Variant
Dim y As Variant
Dim strFileName As String
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("idrmterm")
x = Format(Date - 7, mm - dd - yy)
y = Format(Date - 1, mm - dd - yy)
Dim strPath As String
strPath = "H:\REPORTS\Every Monday Reports\"
'===strFileName = x & " " & y & ws & ".xls"<<<===
ActiveWorkbook.SaveAs Filename:=strPath & strFileName
Set wb = Nothing
Set ws = Nothing
Application.Dialogs(xlDialogSendMail).Show
ActiveWorkbook.Close
End Sub

Thanks Tom.

It works, except that the dates are not coming up as mm-dd-yy instead
mm/dd/yy and it won't save the file. What do you suggest?

This is how it reads now:

Dim wb As Workbook
Dim ws As Worksheet
Dim x As Date
Dim y As Date
Dim strFileName As String
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("idrmterm")
x = Format(Date - 7, mm - dd - yy)
y = Format(Date - 1, mm - dd - yy)
Dim strPath As String
strPath = "H:\REPORTS\Every Monday Reports\"
strFileName = x & " " & y & ws.Name & ".xls"
ActiveWorkbook.SaveAs Filename:=strPath & strFileName
Set wb = Nothing
Set ws = Nothing
Application.Dialogs(xlDialogSendMail).Show
ActiveWorkbook.Close

Try

x = Format(Date - 7, "mm - dd - yy")
y = Format(Date - 1, "mm - dd - yy")

Putting in "" worked for me on asimilar thing

"CV323" wrote:

Thanks Tom.

It works, except that the dates are not coming up as mm-dd-yy instead
mm/dd/yy and it won't save the file. What do you suggest?

This is how it reads now:

Dim wb As Workbook
Dim ws As Worksheet
Dim x As Date
Dim y As Date
Dim strFileName As String
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("idrmterm")
x = Format(Date - 7, mm - dd - yy)
y = Format(Date - 1, mm - dd - yy)
Dim strPath As String
strPath = "H:\REPORTS\Every Monday Reports\"
strFileName = x & " " & y & ws.Name & ".xls"
ActiveWorkbook.SaveAs Filename:=strPath & strFileName
Set wb = Nothing
Set ws = Nothing
Application.Dialogs(xlDialogSendMail).Show
ActiveWorkbook.Close