macro to navigate & close several excel files

[jacobrcotton]

Hello Everyone,

I don't really know how to approach this problem, but I'm hoping for some guidance. Semi-related previous post: https://www.mrexcel.com/forum/excel...-xlsx-then-find-leftmost-tab.html#post5097663

I have a code that will make an excel file "client ready" that is saved in my PERSONAL workbook so that i can use it on all excel files. The problem is that in doing so, we are working with 3 different spreadsheets, and I'd like a specific spreadsheet to close at the end of the run. The process we are following is:

(1) doing work via WORK workbook
(2) opening PERSONAL workbook
(3) run "make this client ready" macro stored in the PERSONAL workbook via the WORK workbook
*Note that the "make this client ready" macro creates a pasted-values-only-copy of the WORK workbook as ".xlsx" named CLIENT READY workbook, see prior post above for details.
**Note that everything is working perfectly up to this point.
(4) manually close WORK workbook but do not save it (as the WORK workbook is a template).
(5) open the CLIENT READY workbook to do checks.

I'd like a code that can be stored in my PERSONAL workbook, run via the WORK workbook, create the CLIENT READY workbook (all this is working so far), open the CLIENT READY workbook, and then close the WORK workbook without saving.

The current codes (which stops at (3) above) are:

Sub MakeClientReady()

    ActiveWorkbook.Save
    
    Call UnhideSheets
    Call CopyPasteValuesAllSheets
    Call DeleteTabs
    Call ClearOutsidePrintArea
    Call FindHomeAllSheets
    Call DeleteAllNames
    Call FindFirstSheet
    Call SaveAsXLSX
    
End Sub

Sub SaveAsXLSX()

    Dim fPath As String, fName As String
    Dim ws As Worksheet
    Dim i As Long
    Dim arr() As String


    fPath = ActiveWorkbook.Path
    fName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))
    ReDim arr(1 To (ActiveWorkbook.Sheets.Count))
    i = 1


    For Each ws In ActiveWorkbook.Worksheets
        arr(i) = ws.Name
        i = i + 1
    Next ws


    Worksheets(arr).Copy
    ActiveWorkbook.SaveAs FileName:=fPath & "\" & fName & " " & Format(Now(), "yyyymmddhhmmss") & ".xlsx"


    Application.DisplayAlerts = False
        ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub