Backup your workbook before trying this macro.



Try this macro, this will prompt whether you want to overwrite , I yes
will overwrite, else it will close workbook without overwriting

Sub CreateArchive()

Dim Wb As Workbook
Dim ws As Worksheet
Dim sStr As String

Application.ScreenUpdating = True

Set Wb = Workbooks("Book1.xls")
Set ws = Worksheets("Sheet1")
sStr = Format(Date, "yymmdd") & " " & "Stage Clearer"

ws.Copy

If file_exist("g:\" & sStr & ".xls") Then
MsgBox "Do you want to overwrite the file", vbYesNo
If vbYes Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "g:\" & sStr
Application.DisplayAlerts = True
End If
Else
ActiveWorkbook.SaveAs "g:\" & sStr
End If
ActiveWorkbook.Close
Application.ScreenUpdating = False
End Sub

Function file_exist(str As String)
If Len(Dir(str)) 0 Then
file_exist = True
Else
file_exist = False
End If
End Function


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=384216