File Path

[STEVEB]

I run a macro each month that calculates certain data and then
automatically names the spreadsheet and saves the data to a folder.

Is there a way to have excel save a file to a prior month folder?

For example, I run a macro in April for March data and the macro will
automatically save the data to:

C:\Finance\Past Due\spreadsheet.xls

I would like Excel to save the data to:

C:\Finance\Past Due\March\spreadsheet.xls

Is there a way to have Excel automatically save the spreadsheet to the
prior month folder?

Monthly folders are already set-up in the Past Due folder

Any help would be greatly aprreciated!

 

[Ken Hudson]

Hi Steve,
Where on the worksheet will the macro find the current month? We'll need a
date to be able to get the prior month into the correct folder.

 

[STEVEB]

Thanks Ken, I appreciate your help!

Cell A2 - Sheet 1.

Please let me know if you have any additional questions

 

[Ken Hudson]

Hi Steve,
With 4/24/06 in cell A2 on Sheet1 this code should save the file to the
"March" folder.

Option Explicit
Dim FileSaveName As String
Dim Mo As String

Sub SaveMonth()

Mo = Month(Sheets(1).Range("A2"))
Mo = Mo - 1
FileSaveName = "C:\Finance\Past Due\" & MonthName(Mo) & "\spreadsheet.xls"
ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False

End Sub

 

[STEVEB]

Thanks Ken,

Everything workd great! I really appreciate your help