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

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.

ram wrote:
>
> 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

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" wrote:

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

Thank you very much this is just what I needed

"JLatham" wrote:

> 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" wrote:
>
> > 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
> >