excel - How to use VBA SaveAs without closing calling workbook?

Question

I want to:

• Do data manipulation using a Template workbook
• Save a copy of this work book as .xlsx (SaveCopyAs doesn't let you change filetypes, otherwise this would be great)
• Continue showing original template (not the "saved as" one)

Using SaveAs does exactly what is expected - it saves the workbook while removing the macros and presents me the view of the newly created SavedAs workbook.

This unfortunately means:

• I no longer am viewing my macro enabled workbook unless I reopen it
• Code execution stops at this point because
• Any macro changes are discarded if I forget to save (note: for a production environment this is ok, but, for development, it's a huge pain)

Is there a way I can do this?

'current code
Application.DisplayAlerts = False
templateWb.SaveAs FileName:=savePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
templateWb.Activate
Application.DisplayAlerts = True

'I don't really want to make something like this work (this fails, anyways)
Dim myTempStr As String
myTempStr = ThisWorkbook.Path & "" & ThisWorkbook.Name
ThisWorkbook.Save
templateWb.SaveAs FileName:=savePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Workbooks.Open (myTempStr)

'I want to do something like:
templateWb.SaveCopyAs FileName:=savePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False 'SaveCopyAs only takes one argument, that being FileName

Also note while SaveCopyAs will let me save it as a different type (ie templateWb.SaveCopyAs FileName:="myXlsx.xlsx") this gives an error when opening it because it now has an invalid file format.

Answer 1

Here is a much faster method than using .SaveCopyAs to create a copy an then open that copy and do a save as...

As mentioned in my comments, this process takes approx 1 second to create an xlsx copy from a workbook which has 10 worksheets (Each with 100 rows * 20 Cols of data)

Sub Sample()
    Dim thisWb As Workbook, wbTemp As Workbook
    Dim ws As Worksheet

    On Error GoTo Whoa

    Application.DisplayAlerts = False

    Set thisWb = ThisWorkbook
    Set wbTemp = Workbooks.Add

    On Error Resume Next
    For Each ws In wbTemp.Worksheets
        ws.Delete
    Next
    On Error GoTo 0

    For Each ws In thisWb.Sheets
        ws.Copy After:=wbTemp.Sheets(1)
    Next

    wbTemp.Sheets(1).Delete
    wbTemp.SaveAs "C:Blah Blah.xlsx", 51

LetsContinue:
    Application.DisplayAlerts = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub