Hi Helmut and Walt,

Thanks for your replies.

From my (very) limited knowledge of VB programming, the code looks as though
it first opens the report workbook, then sees if there is already a current
month file. If not it then saves the workbook as MMMYY.xls (or something
similar). Is this correct?

Is it possible to check whether the file exists first and then, if it
doesn't, open the report workbook and save it as the month name? I intend to
run this code from a workbook (program.xls) other than the report workbook,
so as is, every time program.xls is open and "OK" is clicked on the opening
sheet, the report workbook would open and the check would occur. This would
slow things down I think.

Also, is there something in this code that says that it only needs to occur
the first time program.xls is opened in a new month?

To clarify, what I would like to do is, on the first day that program.xls is
opened in October, save the data that had been entered into report.xls as
September05.xls (or similar) and clear report.xls so that Octobers data can
be saved into it (and then saved as October05.xls in November.

Perhaps it is easier to create October05.xls the first time program.xls is
run in October and save all of Octobers data to that. Then November05.xls the
first time the program is run in november etc etc.

Sorry for these silly question, but I am very new to VB programming.

"Walt" wrote:

Hi Helmut,

The following is a bit simpler:

Sub Workbook_Open()
Dim OldName As String
Dim OldPath As String
Dim NewName As String
Dim NewPath As String

OldPath = ActiveWorkbook.Path & "\"
OldName = ActiveWorkbook.Name

NewPath = "c:\reports\"
NewName = Format(Date, "YYYY-MM") & ".xls"
If Dir(NewPath & NewName, vbNormal) = "" Then _
ActiveWorkbook.SaveCopyAs NewPath & NewName
End Sub

Best Regards,
Walt Weber


Helmut Weber wrote:
Hi,

like this:

Sub Workbook_Open()
Dim OldName As String
Dim OldPath As String
Dim NewName As String
Dim NewPath As String

OldPath = ActiveWorkbook.Path & "\"
OldName = ActiveWorkbook.Name

NewPath = "c:\reports\"
NewName = Format(Date, "YYYY-MM") & ".xls"
Application.DisplayAlerts = False
If Dir(NewPath & NewName, vbNormal) = "" Then
ActiveWorkbook.SaveAs OldPath & OldName
ActiveWorkbook.SaveAs NewPath & NewName
Workbooks.Open OldPath & OldName
Workbooks(NewName).Close
End If
End Sub

Remember the name of the active workbook
get year and month, create a filename form it,
check whether such a file already exists,
if not, save the active workbook with that name.
open the old workbook,
close the new workbook.

Clearing all data is not a problem, I assume.


Helmut Weber