I am trying to finalise my archive procedure and am facing two problems
that I would greatly appreciate a hand with.

Currently the procedure creates an archive of a master file and and
gives the user an option to not proceed if a file with the same name
already exists (msg box). However if the do not proceed option is taken
(NO) then I am left with the copied and unnamed file open.

How can I get rid of this unwanted copy and navigate back to a
specified sheet.

Thanks in advance for any input.



' creates archive copy of stage clearance master data in specified
location

Sub CreateArchive()

Dim Wb As Workbook
Dim ws As Worksheet
Dim sStr As String
Dim Res As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet

Application.ScreenUpdating = False

Set Wb = Workbooks("Single Sheet.xls")relabeled
Set ws = Worksheets("Master")
Set ws1 = Worksheets("stageclearer 1")
Set ws2 = Worksheets("stageclearer 2")
Set ws3 = Worksheets("stageclearer 3")
Set ws4 = Worksheets("stageclearer 4")

sStr = Format(Date, "yymmdd") & " " & "Stage Clearer"

ws.Copy


ws1.UsedRange.Offset(1).Clear
ws2.UsedRange.Offset(1).Clear
ws3.UsedRange.Offset(1).Clear
ws4.UsedRange.Offset(1).Clear
ws.UsedRange.Offset(1).Clear

If file_exist("G:\" & sStr & ".xls")

Res = MsgBox("This file already Exists. " & _
"Do you want to overwrite it?", vbYesNo + vbCritical, "OVERWRITE
FILE")
If Res = vbYes Then


Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "G:\" & sStr
ActiveWorkbook.Close

Application.DisplayAlerts = True

End If

Else

ActiveWorkbook.SaveAs "G:\" & sStr

Worksheets("Navigation").Select

End If

Application.ScreenUpdating = True

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


--
Kstalker
------------------------------------------------------------------------
Kstalker's Profile: http://www.excelforum.com/member.php...o&userid=24699
View this thread: http://www.excelforum.com/showthread...hreadid=385437