MrExcel.com - Your One Stop for Excel Tips & Solutions

Save As Keeping Original Open

June 05, 2018 - by

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

Video Transcript

Learn Excel from MrExcel Podcast, Episode 2213: Save As Using VBA, But Keep the Original Open.

Hey, welcome back to the MrExcel netcast. I'm Bill Jelen. Well, you might have noticed that I've been offering the download of each podcast recently because a lot of people have been asking me for that, and so I was trying to make life as easy as possible. And the whole goal was to save a copy that you can download, but I didn't want the extra stuff-- you know, the stuff that's for my own internal use-- there, so I wanted to get rid of that.

And, you know, let's say that I had a situation where I had to write 12 workbooks, right? Each with a different product. So, I'm going to loop through these products and I'm going to write it there to A2 and then save the workbook, and maybe clean up some stuff. Alright. So my first pass through here is a macro like this, alright? So, we define the current workbook-- Worksheets("Data), Worksheets("Report") to find those-- and then figure out how many rows of data we have today, we're going to loop from Row 2 down to the final row, copy the product from the data workbook over to the report workbook.

Alright, and now here's where I'm about to get into trouble. So the new workbook is going to be called "C:\aaa\" and then Apple.xlsx, and I'm going to Save As a, you know, with Apple.xlsx, and change to XML-- open xml workbook-- which will strip the macros out. Alright. But now I'd like to close that workbook, but unfortunately when you do a Save As-- see right now, I'm in podcast 2013-- when I do a Save As after that point in the code, I'm no longer going to be in Podcast 2013; I'm going to be in Apple.xlsx. Alright? So, now, if I want to start deleting stuff, I'm going to be deleting it in the copy, but when I close the copy, well, I can't get back to the original file. Alright? And this macro-- actually, my head's about to explode trying to figure out if the loop will still work or not work, right? So I think Save As is the wrong way to go here.

Well, actually, hang on. We could go two routes: First, I could have another workbook that opens Podcast 2213, does the stuff and then Saves As with the new name, or I'm going to go this way, alright, and this is the method I ended up using-- alright, and we're going to define this workbook, but then also a new workbook. Right. And everything is kind of the same down here until we get to the point where I was about to do WBT.SaveAs. Check this out: SaveCopyAs-- now, this doesn't exist, as far as I can tell, in regular Excel... this is VBA only. SaveCopyAs says, "Hey, look we're in a file called 2213 and I want you to take that file 2213 in its current state, and save it to disk, and close.” Keep the original file open-- 2213 stays open-- but now we have a brand new file on disk called Apple.xlsm. Actually, at first, I'm just going to call it DeleteMe.xlsm. Alright. But it creates an identical copy and keeps the original file-- the file that the macro is running in-- open, and that's the important part, right? So now that I have DeleteMe out there, I open it, assign it to WBN, do the things I need to do, get rid of all the extra sheets-- I know what I have. Notice, before you delete the sheets, you want to do DisplayAlerts = False, otherwise it keeps asking you, "Hey, you're not going to get the sheet back." I get it. And then, finally here, select the first worksheet FN is going to be Apple.xlsx, and then we can do WBN.SaveAs Apple, as an Open XMLWorkbook. No macros. And then Close-- the beautiful thing about Close is I'm now back in this workbook, 2213.

Alright, it's been working really, really well, and the key to this whole thing is SaveCopyAs-- SaveCopyAs. So, to me-- well that's been around for a long time-- I never used it, and now I realize there's probably been a lot of times in the past where I should have used it. And maybe, you know, you should be using it or consider using it as well.

Okay, I forgot to mention one thing: the important thing is, with Save As Copy-- Save As Copy-- so when I do the Save As Copy, if I try to change to an xslx file type, I cannot change the file type here just by changing the extension in NewFN and when trying to open that subsequent workbook, they'll detect that it has macros, and it has the wrong extension, and it will yell at you. Right? So, you have to save it as xlsm and then later come back, reopen it, and save it as xlsx. But that all works with this macro.

So, hey, when I'm updating this book, Excel 2016, this summer, for Excel 2019, I'll make sure to include this tip. I think it's a useful tip to SaveCopyAs.

Wrap-up from today: You want VBA to write several copies of the current workbook; Save As causes problems because the original workbook is no longer open; instead you use .SaveAsCopy to save a copy of the workbook. If you want to download the workbook from today's video, including the macro, visit the URL in the YouTube description.

I want to you for stopping by, I'll see you next time for another netcast from MrExcel.

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