Macro Selects Directory to Save File Based on Sheet Criteria

[watchtower]

I am trying to set up a macro that looks in the contents of a cell, for
example E9, and determines the file path and file name to save the file. E9
will contain a date. Based on the date, the macro will need to select the
correct folder:

Cell E9 = 8/9/07
File path & name = C:\2007\(08) August\MCBS 08-09-2007.xls

Any suggestions?

I appreciate it.
Adam

 

[Bob Phillips]

mpPath ="C:\" & Year(Range("E9").Value) & "\(" & Format(Range("E9").Value,
"mm) mmmm") & "\MCBS " & Format(Range("E9").Value, "mm-dd-yyyy")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim Jackson]

a = Format(Range("E9"), "yyyy") ' 2007
b = Format(Range("E9"),"("& "mm" &")") ' (08)
c = Format(Range("E9"),"mmmm") ' August

ActiveWorkbook.Saveas Filename:="C:\" & a & "\" & b & " " & c & "\MCBS
08-09-2007.xls"