MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Save As Keeping Original Open


June 05, 2018 - by Bill Jelen

Save As Keeping Original Open

There are many times in a month where I need an Excel workbook to create many copies of itself. I usually get into a logic bug by opening *the* workbook and I start to write code to loop through a list and use File, Save As to save a copy of the workbook.

Here is the flowchart:

Logic error when I close the workbook
Logic error when I close the workbook

Do you see the problem above? The macro is running in WorkbookA. When I Save As the file as RegionEast.xlsx and then close RegionEast.xlsx, the macro will stop running.

I am usually deep into the pseudocode before I see the problem.


Sub MakeCopies()
    Dim WBT As Workbook
    Dim WSD As Worksheet
    Dim WSR As Worksheet
    Set WBT = ThisWorkbook
    Set WSD = WBT.Worksheets("Data")
    Set WSR = WBT.Worksheets("Report")
    
    FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To FinalRow
        ' Copy this product to WSR.B2
        WSR.Cells(2, 1).Value = WSD.Cells(i, 1).Value
        
        ' Save this workbook with a new name
        NewFN = "C:\aaa\" & WSD.Cells(i, 1).Value & ".xlsx"
        ' Save As a file type without macros
        WBT.SaveAs Filename:=NewFN, FileFormat:=xlOpenXMLWorkbook
        ' Close the new workbook
        ' But that won't work because I need this workbook to stay open
    
End Sub

Had I been thinking ahead, I would have created a two-workbook solution. Put all the macros in WorkbookA. Put all of the data in WorkbookB. Have WorkbookA repeatedly open WorkbookB, change the data, SaveAs, Close.

Slightly more complicated
Slightly more complicated

As I get older and crankier, I am finding that I am less in the mood to go with slightly more complicated. Especially since I have most of the code working for the original SaveAs command.

Today's article is about the awesome VBA method for SaveAsCopy. This command will keep WorkbookA open and called WorkbookA. The macro can keep running. But it will write out the current state of the workbook to a new closed workbook called WorkbookB.

This allows me to go back to the original flow chart:

Simpler logic, all self-contained
Simpler logic, all self-contained

However, I discovered one problem with SaveAsCopy. When I do ThisWorkbook.SaveAs, I can choose if I want to save as XLSX or XLSM. If I need the macros to be available in the new workbook, then I use XLSM. Otherwise, I use XLSX and the macros will disappear.

Unfortunately, if you are in an XLSM workbook, you can not successfully .SaveAsCopy and change to XLSX. The code will work. But the resulting workbook will not open as Excel detects a mis-match between file type and the file extension.

My solution is to SaveAsCopy as XLSM. Once the copy is saved, I can open the workbook (creating two copies of the workbook in memory) and then SaveAs XLSX.

Sub MakeCopies()
    Dim WBT As Workbook
    Dim WBN As Workbook
    Dim WSD As Worksheet
    Dim WSR As Worksheet
    Set WBT = ThisWorkbook
    Set WSD = WBT.Worksheets("Data")
    Set WSR = WBT.Worksheets("Report")
    
    FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To FinalRow
        ' Copy this product to WSR.B2
        WSR.Cells(2, 1).Value = WSD.Cells(i, 1).Value
        
        ' Save this workbook with a new name
        FN = "C:\aaa\" & WSD.Cells(i, 1).Value & ".xlsx"
        
        ' Save a Temp Copy of this workbook
        NewFN = "C:\aaa\DeleteMe.xlsm"
        ' Delete any previous copy left over from before
        On Error Resume Next
        Kill (NewFN)
        On Error GoTo 0
        
        ' ****************************************
        ' Use SaveCopyAs instead of SaveAs
        WBT.SaveCopyAs Filename:=NewFN
        
        ' Open the workbook we just saved
        Set WBN = Workbooks.Open(NewFN)
    
        ' Delete the worksheets that we don't need
        Application.DisplayAlerts = False
        For Each WS In WBN.Worksheets
            Select Case WS.Name
                Case "BuyTheBook", "Info", "Form", "Template", "Article", "NotesForApp", "Data"
                    WS.Delete
            End Select
        Next WS
        Application.DisplayAlerts = True
    
        NewFN = FN
        WBN.Worksheets(1).Select
    
        On Error Resume Next
        Kill (NewFN)
        On Error GoTo 0
    
        Application.DisplayAlerts = False
        WBN.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
        Application.DisplayAlerts = True
    
        WBN.Close False
    
        ' Delete the temporary copy
        NewFN = "C:\aaa\DeleteMe.xlsm"
        On Error Resume Next
        Kill (NewFN)
        On Error GoTo 0
End Sub

Watch Video

Download Excel File

To download the excel file: save-as-keeping-original-open.xlsm

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Dates are numbers, not words."

Title Photo: Andrew Bui on Unsplash


Bill Jelen is the author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.