[2003] Excel+VBA: Save old doc to new location/delete previous doc

[westconn1]

there are several ways this could be achieved

one that springs to mind is to put the filename (with path) into an empty cell prior to doing saveas

Workbooks("jw.xls").Sheets("sheet1").Range("x1") = Workbooks("jw.xls").FullName ' select an empty cell somewhere
Workbooks("jw.xls").SaveAs "jw2.xls"

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Not IsEmpty(Sheets("sheet1").Range("x1")) Then
    If Not Dir(Sheets("sheet1").Range("x1")) = "" Then
        Kill Sheets("sheet1").Range("x1")
        Sheets("sheet1").Range("x1") = ""
    End If
End If
 
End Sub

note this could cause problem if later you wanted to store a filename in the selected cell, also the code will run everytime the selection changes
you could put the delete code into the workbook open event, but it will not run until the next time the file is opened, but that may not matter,
there will be other ways to do the same thing