DavidH56,

Try something along the lines of what is listed below. (The code is
untested).

Best,

Matthew Herbert

Dim strFName As String

Select Case Weekday(Now())
Case 1, 7
strFName = "C:\Daily Report\Archives\DAILY REPORT" & Format(Date,
"dd-mmm-yyyy") & ".xls"
Case 2 To 6
strFName = "C:\Daily Report\" & Format(Date, "dddd") & "\DAILY
REPORT" & Format(Date, "dd-mmm-yyyy") & ".xls"
End Select

ActiveWorkbook.SaveAs Filename:=strFName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

"DavidH56" wrote:

Hi,

Thanks all of you experts for being such a great help in solving my
programming issues. I would like to know the best method for simplyfying this
bit of code:

Private Sub Save_DAILY_REPORT()
'TO SAVE REPORT FILE
If Weekday(Now()) = 1 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If

If Weekday(Now()) = 2 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Monday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 3 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Tuesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 4 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Wednesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If

If Weekday(Now()) = 5 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Thursday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 6 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Friday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 7 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If


Thanks in advance for your help.