MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Saving a worksheet


Posted by Roger Black on October 31, 2001 5:04 AM

Good day to all,

Is there any code I can use to prompt the user to:

Input the month and year being processed when they open the worksheet and putting that information in a certain cell?

Have the worksheet saved as the month and year - eg January2001- when it is saved for the first time?


I have managed to save and exit the worksheet but i am am not quite sure what code i should use along with what is below to have it saved as month/year.


For each w in application.workbooks
w.save
next w
application.quit
Any help would be appreciated


Posted by Joe Chin on October 31, 2001 10:41 AM

You could use something like this:

Private Sub Workbook_Open()
Dim strMonthAndYear as String

strMonthAndYear = InputBox("Enter the month and year to be processed")
ThisWorkbook.Worksheets(1).Range("A1").Value = strMonthAndYear

End Sub

Private Sub Workbook_BeforeSave()
Dim strNewName as String

strNewName = ThisWorkbook.Worksheets(1).Range("A1").Value
If ThisWorkbook.Name <> strNewName Then
ThisWorkbook.SaveAs strNewName
End Sub

I haven't tested this, but that should at least give you an idea of what to do.