Kill file and save as

[Pixelpusher]

This has been buging me for a few days I realy need to resolve this one.
I have the code below to save the active workbook to a location as a defined name, what I need is to add code to deleat(Kill) the original file and close new saves workbook without a promt.

I have read lots of threads on this but with errors one deleated my file with the macros attached thanks.

'=====================================================
Sub ArcivePDF()

Dim Response As Integer
' Displays a message box with the yes and no options.
Response = MsgBox(Prompt:="Please check for any empty RED cells.Are you sure you want to save as final CDS [.PDF].'Yes' or 'No'.", Buttons:=vbYesNo)
' If statement to check if the yes button was selected.
If Response = vbYes Then
Dim sPath As String
'Change to CDSARCIVE location.
sPath = "F:\PGregory\EDEC\CDSARCIVE\"
On Error Resume Next
ActiveWorkbook.SaveAs sPath & ActiveSheet.Range("B6").Value & ".xls"
If Err.Number <> 0 Then
MsgBox "File not saved!" & vbLf & "Check if all referance Numbers are Entered"
Err.Clear
End If
Else
' The no button was selected.
MsgBox "You selected 'No'."
End If
End Sub
=====================================================

 

[Norie]

Does this work?

Sub ArcivePDF()
Dim Response As Integer
Dim sPath As String
Dim strOldFileName

    ' Displays a message box with the yes and no options.
    Response = MsgBox(Prompt:="Please check for any empty RED cells.Are you sure you want to save as final CDS [.PDF].'Yes' or 'No'.", Buttons:=vbYesNo)
    ' If statement to check if the yes button was selected.
    If Response = vbYes Then

        'Change to CDSARCIVE location.
        sPath = "F:\PGregory\EDEC\CDSARCIVE\"
        strOldFileName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
        
        On Error Resume Next
        
        ActiveWorkbook.SaveAs sPath & ActiveSheet.Range("B6").Value & ".xls"
        
        Kill strOldFileName
        
        If Err.Number <> 0 Then
            MsgBox "File not saved!" & vbLf & "Check if all referance Numbers are Entered"
            Err.Clear
        End If
        
    Else
    ' The no button was selected.
    MsgBox "You selected 'No'."
    End If
    
End Sub

 

[Pixelpusher]

Kill file and save as (Plus)

Your a star Norie it works as I explaned it but I have an added problem if the file is a template the code shows the MsgBox "File not saved!" & vbLf & "Check if all referance Numbers are Entered" but infact the file is saved.
maybe I should explane what I am doing.Buy the way what is the connection with Lima?.

I have a number of master workbooks in template format (*XLt) the end user opens a template from a list in a userform, works on it then saves it as a nomal excel file with the macro prevously posted it will then be converted later to a PDF. now if for any reason the sheet is not completed then it is saved in a differant folder (Work in Progress) by means of another macro.
It then can be opened again completed and saved with the macro previosly posted. So its only if the sheet is opened from the work in progress folder that needs to be killed when its saved.the file name will always have "WIP" in front of it Any ideas wellcome.