Macro to create a new folder according to the month
 

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

Macro to create a new folder according to the month
 

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.

"Dolphin" wrote:

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

Macro to create a new folder according to the month
 

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

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

Kassie Kasselman


"JLatham" wrote:

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.

"Dolphin" wrote:

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

Macro to create a new folder according to the month
 

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

"kassie" wrote:


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

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

Kassie Kasselman


"JLatham" wrote:

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.

"Dolphin" wrote:

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

Macro to create a new folder according to the month
 

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
--
Hth

Kassie Kasselman


"Dolphin" wrote:

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

"kassie" wrote:


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

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

Kassie Kasselman


"JLatham" wrote:

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.

"Dolphin" wrote:

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

Macro to create a new folder according to the month
 

I noticed the lack of the "\" between myNewFolder and myMonth - I took it
that you just wanted a really odd looking file (folder) name. Kassie's
explanation of why you got the 1004 error once you added that into the fully
formed path is right on target -

You can use DIR to test to see if the folder exists, and if not, then use
MkDir to create it before doing the save.

"Dolphin" wrote:

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

"kassie" wrote:


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

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

Kassie Kasselman


"JLatham" wrote:

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.

"Dolphin" wrote:

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