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

This code closes the activeworkbook without saving it (In Excel 2000)

ActiveWorkbook.Saved = True
ActiveWorkbook.Close

build an if statement to turn it loose...

--
steveB

Remove "AYN" from email to respond
"Kstalker" wrote in
message ...

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

Hi Kristan,

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

Move the line:

ws.Copy

to a postion immediately after the line:

If Res = vbYes Then

That way you only create the copy if it is needed.


BTW, the line:

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

should be:

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


---
Regards,
Norman



"Kstalker" wrote in
message ...

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

Thanks Steve / Norman

Was not completely clear.

I need to discard the worksheet that was decided not to copied. Keep
the workbook that it is being archived from open and navigate back to a
sheet within it when overwrite existing file is declined.

Any ideas?

Kristan


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

Hi Kristan


"Kstalker" wrote in
message ...

Thanks Steve / Norman

Was not completely clear.

I need to discard the worksheet that was decided not to copied. Keep
the workbook that it is being archived from open and navigate back to a
sheet within it when overwrite existing file is declined.

Any ideas?

Kristan


Did my suggestion not do exactly this?

The point of my suggestion is that the worksheet is not created if not
needed and, hence, no deletion is required.

Incidentally, it is not a worksheet, but a single-sheet workbook.


---
Regards,
Norman

Hi Norman.

On the initial archive request when there is no pre-existing file with
same name and yes is selected to create archive workbook it actually
creates an entire copy of the 'Single Sheet' workbook as opposed to a
specific worksheet within the book. It becomes the open workbook and
closes "Single Sheet".

What do you think? I am at a loss.

Kristan


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

Hi Kristan,

Before responding to your comments, please confirm *which* worksheet in the
SingleSheet.XLS workbook you want to archive. It is only one?


---
Regards,
Norman



"Kstalker" wrote in
message ...

Hi Norman.

On the initial archive request when there is no pre-existing file with
same name and yes is selected to create archive workbook it actually
creates an entire copy of the 'Single Sheet' workbook as opposed to a
specific worksheet within the book. It becomes the open workbook and
closes "Single Sheet".

What do you think? I am at a loss.

Kristan


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