Simplify save code

[DavidH56]

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.

 

[Matthew Herbert]

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

 

[Jacob Skaria]

Try the below

Private Sub Save_DAILY_REPORT()
ActiveWorkbook.SaveAs Filename:="C:\Daily Report\" & _
IIf(Left(Format(Date - 5, "ddd"), 1) = "S", "Archives", _
Format(Date, "dddd")) & "\DAILY REPORT" & _
Format(Date, "dd-mmm-yyyy") & ".xls", FileFormat:=xlNormal
End If

If this post helps click Yes

 

[DavidH56]

Thanks Matthew for such a quick response. This is exactly what I was looking
for. Works great. Thanks a million once again.

 

[DavidH56]

Thanks for your quick response Jacob. I first got the end if block error. I
changed spelling for the first if. I now get syntax error.

Thanks

 

[JLGWhiz]

Just a note for the OP edification. IIf() is a good VBA syntax.

Returns one of two parts, depending on the evaluation of an expression.

Syntax

IIf(expr, truepart, falsepart)

 

[Jacob Skaria]

Did you copy/paste the code? or modified.

If this post helps click Yes

 

[Rick Rothstein]

The first "if" was not spelled wrong... IIf is a valid function. You could
look it up in the VB help files, but here is an online link to the VB help
file for this function...

http://msdn.microsoft.com/en-us/library/aa445024(VS.60).aspx

 

[DavidH56]

I copy and pasted your code. When copied as is I get compile error at Enf If
saying End If without Block If.

 

[Rick Rothstein]

In looking at Jacob's code quickly, it appears he added the End If statement
by mistake. Copy/Paste the code he posted but leave off the End If statement
and I think his code will then work for you (although I should say that did
not directly test his code).

 

[DavidH56]

Ah yes Thanks Rick. It works after I removed the End If just as you said. You
guys are the best.

And Thanks again to Jocob for the code.

 

[Jacob Skaria]

Thanks Rick for picking that up.

David, I starting working on your code and missed to remove the End
If..Removing that should work...and notice the IIF() which is very useful but
not seen to be used extensively....

If this post helps click Yes