Save macro to specific folder by date

Hi,
I have copied a save macro that saves a worksheet to a specific folder based on the date in a specific cell. The document does save to the right folder to a certain extent, but not to the specific date folder within that folder. The macro I am using is below, if anyone can see why this is please let me know. The original macro was saving to a folder 2005-2007 and i simply deleted this section and started from 2010. Not sure if this is the source of the problem. Many thanks

Formula in K1 is =YEAR(J7) formula in L1 is =E7&""&K1

If Range("E7") = "" Then
MsgBox "Please Enter Airline Name"
Range("E7").Select
ElseIf Range("J7") = "" Then
MsgBox "Please Enter Inception Date"
Range("J7").Select
ElseIf Range("K1") = 2010 Then
ActiveWorkbook.SaveAs Filename:="H:\Models\Saved Models\Air Traffic Control Saved Models\2010" & Range("L1")
MsgBox "A Copy of this Model Has Now Been Saved in: Models\Saved Models\Air Traffic Control Saved Models\2010"
ElseIf Range("K1") = 2011 Then
ActiveWorkbook.SaveAs Filename:="H:\Models\Saved Models\Air Traffic Control Saved Models\2011" & Range("L1")
MsgBox "A Copy of this Model Has Now Been Saved in: Models\Saved Models\Air Traffic Control Saved Models\2011"
ElseIf Range("K1") = 2012 Then
ActiveWorkbook.SaveAs Filename:="H:\Models\Saved Models\Air Traffic Control Saved Models\2012" & Range("L1")
MsgBox "A Copy of this Model Has Now Been Saved in: Models\Saved Models\Air Traffic Control Saved Models\2012"


End If



End Sub

First, please review the forum rules. Code should be included in code tags (see my signature below for instructions). Using code tags preserves the formatting of the code to make it more readable (assuming it's formatted to be readable in the first place )

It is not clear what you are expecting to happen. What your code will do is save the file in the form of

H:\Models\Saved Models\Air Traffic Control Saved Models\2010<airlinename><year>

so you will get files like

H:\Models\Saved Models\Air Traffic Control Saved Models\2010United2010

H:\Models\Saved Models\Air Traffic Control Saved Models\2011USAir2011

Maybe you want this:

ActiveWorkbook.SaveAs Filename:="H:\Models\Saved Models\Air Traffic Control Saved Models\2010\" & Range("L1")

Note the backslash added after the year, to make it a folder. Now you will get

H:\Models\Saved Models\Air Traffic Control Saved Models\2010\United2010

Also, a you could do a little streamlining, to just have one case cover all the years:

Else
   Dim fname as String
   fname = "H:\Models\Saved Models\Air Traffic Control Saved Models\" & Range("K1") & "\" & Range("L1")
   ActiveWorkbook.SaveAs Filename:=fname
   MsgBox "A Copy of this Model Has Now Been Saved in: " & vbCrLf & fname
End If

Note that I also updated your MsgBox call to include the entire file name, not just the directory. This can also be improved by storing the full path name of the file in a variable and using it in those two places. It guarantees that they will be the same. I added a line break in your message for a nicer presentation to the user.

By the way, I do not see why you added a null string in the middle of

=E7&""&K1

Did you mean for that to be a space? Otherwise, it is equivalent to

= E7&K1

Thanks fo your help. Sorry joined in a hurry as working to a deadline and didnt realise code should be included in code tags.

Will try your simplified version, much better than the current one! I am a complete novice so I am trying to simplify things by copying existing macros and adapting them, to limited success.

Thanks again.