Macro to create a new folder according to the month

[Guest]

Hi,

I wanted to have my macro to save my file into a new folder according to the
month it was for (ie, a new folder will be created every month). My macro
goes like this, but it can't seem to work:

Dim myMonth As String
Dim myNewFolder As String

myMonth = Format(Range("c4"), "yymm")
myNewFolder = Format(Range("c4"), "MMM")
myFileName = "M:\RECONCILIATIONS\" & myNewFolder & myMonth & "
SG51-1421000"
ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal

Thanks,
Dolphin

 

[Guest]

You didn't tell us what is in C4 when this is run. Make sure that what is in
C4 is actually a date, and that C4 is formatted as a date. Basically your
code works for me with that stipulation.

 

[Guest]

I notice there is no \ between your folder name and file name?

myFilnename="M:\RECONCILIATIONS\" & myNewFolder & "\" & myMonth &
"SG51-1421000"

 

[Guest]

Hi,

Cell C4 is a date format. The macro works well if I do not add the
myNewFolder in. Upon adding it in (and include your comment whereby I should
add "\"), I was given a runtime error 1004. Why is this so?

Regards,
Dolphin

 

[Guest]

What I normally did, was to create the month folders in advance. Your
problem here is that you try to create a new folder, without using MkDir!
You will either have to add a line of code to create the new directory, using
MkDir, before doing the save, or else manually create the new directory. If
you do it with code, once the directory is there, you are again going to get
an error