Macro that saves the file as macro-free closes the original file

Question

Here is a snippet of the last part of a data manipulation macro:

    Cells.EntireColumn.AutoFit

    Application.ScreenUpdating = True

    Dim fullfilenamelength As Integer, filenamelength As Integer
    fullfilenamelength = Len(ThisWorkbook.FullName)
    filenamelength = Len(udfWBFilename("ThisOne"))

    Dim newFilePath As String, newFileFullName As String
    newFilePath = Left(ThisWorkbook.FullName, fullfilenamelength - filenamelength)
    newFileFullName = newFilePath & "Aspects List.xlsx"

    ActiveWorkbook.SaveAs Filename:=newFileFullName, FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=False

    Workbooks.Open Filename:=newFileFullName
    Windows("Aspects List.xlsx").Activate

    Beep
    Application.DisplayAlerts = True
    Application.StatusBar = False
End Sub

Here at the end, it saves the file as a macro-free workbook, then opens the new file.

Why does it close the old file when doing so? (in other words, macro execution is stopped after running the line Windows("Aspects List.xlsx").Activate - the subsequent lines are never executed.)

score 1 · Accepted Answer · edited May 23 '17 at 12:06

Just remove this line

Workbooks.Open Filename:=newFileFullName

After performing ActiveWorkbook.SaveAs, your active workbook already refers to Aspects List.xlsx:

Before SaveAs:

enter image description here

After SaveAs:

enter image description here

Btw, it seems to me that

newFilePath = Left(ThisWorkbook.FullName, fullfilenamelength - filenamelength)

could be simplified to

newFilePath = ThisWorkbook.Path & "\"

Also it may be interesting: How to avoid using Select/Active statements

Macro that saves the file as macro-free closes the original file

1 Answers1