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