Question about saving a workbook with a macro

[Colombo]

I've got a question about saving a workbook with a macro. I can save the workbook fine with the macro. What I would like to do is have the macro generate the file name based upon a given text & add the month year into the title of the file name.

So for example, I want to save the file name as "Feb 2003 Monthly Report.xls"

Is there anyway to setup the macro to use the date function or something else to generate the Feb 2003 based upon the current month, or possibly by looking at a cell in the workbook?

Thanks in advance for any help.

Josh

 

[Joe4]

In VBA, you can generate the current Month and Date in the format you requested like this:

MyDate = Format(Now(), "mmm") & " " & Year(Now())

 

[gnaga]

This will do the job

ThisWorkbook.SaveAs (Format(Month(Now()), "mmm") & " " & Format(Year(Now()), "####") & " Your File Name.xls")

GNaga

 

[Joe4]

I can actually make the formula I posted even simpler:

MyDate = Format(Now(), "mmm yyyy")

 

[WillR]

If you want to use a cell.... name a cell in the workbook as Filename

place the following formula in the cell =TEXT(TODAY(),"mmm yyyy")&" Monthly Report"

then use the following macro

Sub filesaver()


ActiveWorkbook.SaveAs FileName:=Range("Filename").Value


End Sub

 

[Colombo]

This will do the job

ThisWorkbook.SaveAs (Format(Month(Now()), "mmm") & " " & Format(Year(Now()), "####") & " Your File Name.xls")

GNaga

Thanks for the code. I've got a weird problem though. It saves it as Jan 2003. My date on the my computer is correct, any ideas why it's doing this?

MyDate = Format(Now(), "mmm yyyy")

That worked. Thanks

If you want to use a cell.... name a cell in the workbook as Filename

place the following formula in the cell =TEXT(TODAY(),"mmm yyyy")&" Monthly Report"

then use the following macro


code:
--------------------------------------------------------------------------------
Sub filesaver()


ActiveWorkbook.SaveAs FileName:=Range("Filename").Value


End Sub
--------------------------------------------------------------------------------

WillR,

Just a quick question, how do I name a cell in the workbook. After looking at how the report will be generated, I think this would be a better way to go.

I have the date coming back from an external database in the format YYYYMM. If I can manipulate that date format into the mmm yyyy format and use it in the file name, I think that would work the best.

Thanks everyone for the help.

Josh

 

[3d]

To name a cell click the cell u want to call ie A1 then click insert > name > define > then type the name of the cell that being Filename A1 now = filename

yayyyyy I knew an answer!!!

 

[Colombo]

Thanks

Josh

 

[Colombo]

One more quick question.

I've got the macro saving the workbook by refrencing the cell I've named Filename.

I want to save the workbook in a folder based off the cell I've called "Foldername"

Do I need to modify the
ActiveWorkbook.SaveAs Filename:=Range("Filename").Value

Or do I go about doing that.

Thanks

Josh

 

[Colombo]

anyone got an idea about creating a foldername based on cell in the workbook?

thanks

Josh