Macro to Save File to Location Folder Based on Date in Cell

Hi All,

I have a macro to save files to a specified location based on content of cells, one of which is a date.

It would be great if the macro could look at the date and decide which year folder then which month folder to use.

So we have a folder 'SCCP' then folders in years '2013' then folders in months 'Jan', etc.

Lets assume "O1" shown below is '2013 07 01 Mon', I would like it to pick folder SCCP > 2013 > July then save as. The below Macro works great when saving just to Factory > Planning > SCCP > 2013. Obviously I do not want to be changing the macro every month to pick up the correct monthly folder.

Can anyone help please?

Sub SaveSCCPFigures()
Dim Path As String
Dim FileName1 As String
Path = "R:\Factory\PLANNING\SCCP\2013\"
FileName1 = ("SCCP " + Format(Range("O1"), "YYYY MM DD DDD") _
+ " " + Format(Range("J1") + " " + Format(Range("H1"))))
ActiveWorkbook.SaveAs Filename:=Path & FileName1 & ".xlsm", FileFormat:=52
End Sub

Appreciated as always
John

Cheers tehneXus!!!

That worked an absolute treat thank you for your time & knowledge.

John