Save activeworkbook then move it to another file directory based on criteria in a specific cell.

[cstuder]

I have two folders - In progress and completed. My original file is saved in the "in progress" file folder. Once the "in progress" file is done, I enter a Date Complete in cell J14. Then I save, close and then open file explorer to move the file to a "completed" folder.

Would someone be able to create a macro to automate this process? If a date is entered on the spreadsheet in J14 (criteria), the macro would save, close the file, then move it to the "completed" folder? I need the macro to skip all other files (located in the In Progress folder) if the Date Complete cell is blank. Thanks in advance.

 

[John_w]

This macro should do everything you asked for. It assumes the "In progress" and "Completed" folders are at the same level.

Public Sub Save_Completed_Workbook()

    With ActiveWorkbook
        If IsDate(.ActiveSheet.Range("J14").Value) Then
            'Save a copy of the active workbook in the Completed folder
            .SaveCopyAs .Path & "\..\Completed\" & .Name
            .Saved = True
            'Delete the original workbook (in the In Progress folder)
            .ChangeFileAccess xlReadOnly
            Kill .FullName
            'Close Excel
            Application.Quit
            .Close False
        End If
    End With
        
End Sub

 

[cstuder]

This macro should do everything you asked for. It assumes the "In progress" and "Completed" folders are at the same level.

Public Sub Save_Completed_Workbook()

    With ActiveWorkbook
        If IsDate(.ActiveSheet.Range("J14").Value) Then
            'Save a copy of the active workbook in the Completed folder
            .SaveCopyAs .Path & "\..\Completed\" & .Name
            .Saved = True
            'Delete the original workbook (in the In Progress folder)
            .ChangeFileAccess xlReadOnly
            Kill .FullName
            'Close Excel
            Application.Quit
            .Close False
        End If
    End With
      
End Sub

Worked perfect! Thank you so much!