1. try setting Draft=ws1.Range("A101").Value
2. MSGBOX(Draft) just before the kill to make sure it has been set
--
Gary's Student


"drucey" wrote:


It's an order form.

If someone saves it as a "draft", cell A101 has a "1" value in.

Now, my macro below is supposed to:

Save as a new name, email it, and IF A101 0, then delete the old
sheet.


Code:
--------------------
Sub Save_Click()
Dim lStr_CurFileName As String
Dim ws1 As Worksheet
Dim wb1 As Workbook

Set ws1 = Sheets("Sheet1")
sent = ws1.Range("A100")
Draft = ws1.Range("A101")


Range("T8").Value = Date
Application.DisplayAlerts = False
On Error Resume Next
myarray = Array("Completed Orders", "Partially Arrived Orders", "Draft Orders", "Placed Orders")
Sheets(myarray).Delete
ThisFile = Range("T3").Value
ThisDept = Range("S3").Value
ActiveWorkbook.SaveAs Filename:="J:\Purchase Orders\FM\Order " & ThisDept & ThisFile
Dim NextNo

If Draft 0 Then
Kill lStr_CurFileName
End If


InvNo = ws1.Range("A100")
NextNo = 1
Range("A100").Select

ActiveCell.Formula = InvNo + NextNo
Range("A1").Select
ActiveWorkbook.Save
With ActiveWorkbook
.SendMail ", _
Subject:="Purchase Order " & Format(Date, "dd/mmm/yy")
Application.DisplayAlerts = True
End With

Range("C46:F47").Select
Selection.ClearContents
ActiveSheet.Shapes("Picture 10").Select
Selection.Delete
ActiveSheet.Shapes("Picture 105").Select
Selection.Delete
Range("E45:F48").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveSheet.Shapes("Picture 106").Select
Selection.ShapeRange.IncrementLeft -1
Selection.ShapeRange.IncrementTop -530
Range("C46").Select
ActiveCell.FormulaR1C1 = "Save Delivery"
Range("C47").Select
ActiveCell.FormulaR1C1 = "Information"
Range("C46:C47").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With




Range("A1").Select
Sheets("Sheet1").Activate
End Sub



--------------------


--
drucey
------------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553
View this thread: http://www.excelforum.com/showthread...hreadid=523878