Problem to re-open original file after save as macro has run

**Luli** · 06-28-2012, 11:11 AM

Hi, I'm new to the Forum and in need of help with a basic macro I'm trying to create. The aim of the macro is to save a new file from an existing one, close this newly created file and after that, open the original file again (the file from where the new one was created). I got the save as and closing the file parts working fine but can't seem to get the original workbook re-opened. Below you'll find the code:

Sub SaveAs_Macro()
'
    Dim MyMonth As String
    Dim myWorkbook As String
    myWorkbook = "Z:\0-Lucia-QA files\Tracking Sheet Tests\New tracking sheet blank.xlsm"
    Application.DisplayAlerts = False
    ActiveWorkbook.Sheets("Summary").Activate
    MyMonth = Format(Range("d2"))
    ActiveWorkbook.SaveAs Filename:= _
    "Z:\0-Lucia-QA files\Tracking Sheet Tests\" & Range("A2") & ("_") & MyMonth & Format(Now, "-yyyy"), _
    FileFormat:=51
    ActiveWorkbook.Close True
    Workbooks.Open Filename:=myWorkbook
    Application.Workbooks(myWorkbook).Activate
 End Sub

Any ideas of what I'm doing wrong?

Thanks a lot in advance, I really appreciate all the hard work you people do here

Best regards!

**Richard Buttrey** · 06-28-2012, 11:51 AM

Hi,

It looks like you are closing the workbook that contains the code for the workbook you are trying to open.

Try creating a workbook variable - say Dim wbTemp As Workbook and set it before you Save the workbook. i.e. Set wbTemp = Activeworkbook

Then open the myWorkbook file and finally close the wbTemp workbook that you've saved with wbTemp.Close.

One other thought. For cases like this you should be creating a blank Template file .xltm type file rather than using a standard .xlsm blank file. When you open the template a subsequent save does not ovewrite the template (unless you deliberately choose to do so) but saves the file with a regular .xlsm extension. It would avoid the problem you've encountered.