Saving templates to workbooks using a macro

[Beckwa]

With my booking system I am creating, a template is opened at the beginning of each month and needs to be then saved as a workbook with the name "BkingSystem-March-02" I was wondering if anyone knew of a macro, or how I could create one, which automatically saves the workbook when activated with the above title, but changes due to the month and year which is it saved in. If anyone could help I would greatly appreciate this, Bex X

 

[Al Chara]

Put the following code in the code for your Workbook:

Private Sub Workbook_Open()
ThisWorkbook.SaveAs Filename:="BkingSystem-" & Month(Date) & "-" & Year(Date)
End Sub

Does this work for you or do you want the Month name to appear?

 

[Beckwa]

Ok I tried that and it saved as "BkingSystem --1999.xls"

Any ideas on how I can sort this?

BEX

 

[Al Chara]

Try this instead:
"BkingSystem-" & Month(Now) & "-" & Year(Now)

What version of excel are you running?

 

[Joe Was]

Try,

Private Sub Workbook_Open()
Dim MyDate
Dim MyMonth
MyDate = Date
MyMonth = Month(MyDate)
ThisWorkbook.SaveAs Filename:="BkingSystem-" & MonthName(MyMonth) & "-" & Year(Date)
End Sub

Hope this helps. JSW

P.S. The code go's in the "ThisWorkBook" module!

Note: I tested the code and it saved the file as: BkingSystem-March-2002.xls in the active directory?
This message was edited by Joe Was on 2002-03-20 14:53
This message was edited by Joe Was on 2002-03-20 14:57

 

[NateO]

Howdy Bex. You may want to verify your computer system's date and time. Double click on the clock on the bottom right of your screen, in the windows task bar and make sure it's current (assuming your using Windows).

Food for thought

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-20 14:50

 

[Beckwa]

I've tried it on both Windows 98 and Windows XP and the same problem occurs, any ideas,

BEX
xx

 

[Al Chara]

What version of excel are you running, and like Nate is implying, your system time and date are completely wrong.

 

[Beckwa]

I've tried it on both Windows 98 and Windows XP and the same problem occurs, any ideas,

BEX
xx

 

[Beckwa]

Thank you all very much for ur help, Joe's method seems to work fine! Cheers! BEX
xx