Create a new folderand name it the previous month

[ram]

HI All,

I would like help with the following:

I would like to create a folder with the name of the current month less one.
Next, I would like to Save As the active file I’m working on with the name
Audit Report for current month less one. I would like to save this audit
report in the folder I just created above.

The code I’m trying to use is below. I think my problems are with the items
in blue

MkDir "\\NS-WARQVFS01\Audit\MonthlyReport\" & Format(((Now) - 1), "mmm_yy")

ActiveWorkbook.SaveCopyAs
Filename:="\\NS-WARQVFS01\Audit\MonthlyReport\Folder name from above
\AuditReport_" & Format(Now, "mmm_yy") & ".xls"

Thanks in advance for any help

 

[Dave Peterson]

Dim NewFolderName as string

NewFoldername = "\\(longstringhere\" & format(date - day(date), "mmm-yy")

on error resume next 'in case it already exists
mkdir newfoldername
on error goto 0

activeworkbook.saveas filename:= newfoldername _
& "\auditreport_" & format(date-day(date),"mmm-yy") & ".xls"

===========
Date - Day(date)
will give the last day of the previous month.
(April 16, 2010 - 16 days is March 31, 2010)

Then just format that the way you want -- But I'd use a 4 digit year.

In fact, I'd use:

format(date - day(date), "yyyy-mm")

Then it would make it easier to sort in chronological order.

 

[JLatham]

I assume that the path all the way out to \MonthlyReport\ actually exists -
otherwise you have to create the sub-folders one at a time. Assuming that -
then here's how I'd probably do it

'early in the module
Dim newName As String

newName = Format(DateSerial(Year(Now()), Month(Now()) - 1, 1), "mmm-yy")
MkDir "\\NS-WARQVFS01\Audit\MonthlyReport\" & newName
and later
ActiveWorkbook.SaveCopyAs _
Filename:="\\NS-WARQVFS01\Audit\MonthlyReport\" & newName & _
Application.PathSeparator & newName & ".xls"

 

[ram]

Thank you very much this is just what I needed

I assume that the path all the way out to \MonthlyReport\ actually exists -
otherwise you have to create the sub-folders one at a time. Assuming that -
then here's how I'd probably do it

'early in the module
Dim newName As String

newName = Format(DateSerial(Year(Now()), Month(Now()) - 1, 1), "mmm-yy")
MkDir "\\NS-WARQVFS01\Audit\MonthlyReport\" & newName
and later
ActiveWorkbook.SaveCopyAs _
Filename:="\\NS-WARQVFS01\Audit\MonthlyReport\" & newName & _
Application.PathSeparator & newName & ".xls"