Save a file in multiple locations

[alang84]

I have an xlsm file that I want to save with a location referenced in cell E2, and then save a copy in two different locations as an xlsx format. The two different locations will be on a sheet called "ABC" in cells E3 and E4. I have no issue saving the xlsm file, but can't save the xlsx files. Anyone have any thoughts?

Sub Save()

ActiveWorkbook.Save

Sheets("ABC").Select

pathForFirstSave = Range("E2").Value
pathForSecondSave = Range("E3").Value
pathForThirdSave = Range("E4").Value

ActiveWorkbook.SaveAs fileName:=pathForFirstSave
ActiveWorkbook.SaveAs fileName:=pathForSecondSave
ActiveWorkbook.SaveAs fileName:=pathForThirdSave

End Sub

E2: =“\\us.global.company.com\public\Folder1\Folder2\Folder3\Folder4\Folder5\Folder6\folder7\SOD Master File.xlsm”
E3: =”\\us.global.company.com\public\Folder1\Folder2\Folder3\Folder4\Folder5\Folder6\folder7\ABC File.xlsx”
E4: =”\\us.global.company.com\public\Folder1\Folder2\Folder3\Folder4\Folder5\Folder6\folder7\XYZ File.xlsx”

 

[Domenic]

Try specifying the file format when saving your workbooks. By the way, there's no need to select your worksheet before proceeding with the rest of your macro. Try the following...

Sub Save()

    ActiveWorkbook.Save
  
    With Sheets("ABC")
        pathForFirstSave = .Range("E2").Value
        pathForSecondSave = .Range("E3").Value
        pathForThirdSave = .Range("E4").Value
    End With
  
    ActiveWorkbook.SaveAs Filename:=pathForFirstSave, FileFormat:=xlOpenXMLWorkbookMacroEnabled
  
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=pathForSecondSave, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
  
    ActiveWorkbook.SaveAs Filename:=pathForThirdSave, FileFormat:=xlOpenXMLWorkbook

End Sub

Note that DisplayAlerts is disabled when saving from .xlsm to xlsx, otherwise you would get a dialog box asking you whether you want to save the workbook as a macro-free workbook. Then DisplayAlerts is enabled again after saving.

Hope this helps!

 

[alang84]

Try specifying the file format when saving your workbooks. By the way, there's no need to select your worksheet before proceeding with the rest of your macro. Try the following...

Sub Save()

    ActiveWorkbook.Save
 
    With Sheets("ABC")
        pathForFirstSave = .Range("E2").Value
        pathForSecondSave = .Range("E3").Value
        pathForThirdSave = .Range("E4").Value
    End With
 
    ActiveWorkbook.SaveAs Filename:=pathForFirstSave, FileFormat:=xlOpenXMLWorkbookMacroEnabled
 
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=pathForSecondSave, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
 
    ActiveWorkbook.SaveAs Filename:=pathForThirdSave, FileFormat:=xlOpenXMLWorkbook

End Sub

Note that DisplayAlerts is disabled when saving from .xlsm to xlsx, otherwise you would get a dialog box asking you whether you want to save the workbook as a macro-free workbook. Then DisplayAlerts is enabled again after saving.

Hope this helps!

This worked so thank you for this. Is it possible to keep the xlsm file open?

 

[Domenic]

You can open a copy of your original workbook before you start saving, and then close the workbook once finished...

Sub Save()

    ActiveWorkbook.Save
    
    With Sheets("ABC")
        pathForFirstSave = .Range("E2").Value
        pathForSecondSave = .Range("E3").Value
        pathForThirdSave = .Range("E4").Value
    End With
    
    ActiveWorkbook.Sheets.Copy
    
    ActiveWorkbook.SaveAs Filename:=pathForFirstSave, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=pathForSecondSave, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    
    ActiveWorkbook.SaveAs Filename:=pathForThirdSave, FileFormat:=xlOpenXMLWorkbook
    
    ActiveWorkbook.Close

End Sub

Hope this helps!

 

[Domenic]

By the way, if the your original workbook is running the macro, you can refer to ThisWorkbook instead of ActiveWorkbook prior to saving...

Sub Save()

    ThisWorkbook.Save
    
    With ThisWorkbook.Sheets("ABC")
        pathForFirstSave = .Range("E2").Value
        pathForSecondSave = .Range("E3").Value
        pathForThirdSave = .Range("E4").Value
    End With
    
    ThisWorkbook.Sheets.Copy
    
    ActiveWorkbook.SaveAs Filename:=pathForFirstSave, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=pathForSecondSave, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    
    ActiveWorkbook.SaveAs Filename:=pathForThirdSave, FileFormat:=xlOpenXMLWorkbook
    
    ActiveWorkbook.Close

Hope this helps!

 

[alang84]

Sub Save() ThisWorkbook.Save With ThisWorkbook.Sheets("ABC") pathForFirstSave = .Range("E2").Value pathForSecondSave = .Range("E3").Value pathForThirdSave = .Range("E4").Value End With ThisWorkbook.Sheets.Copy ActiveWorkbook.SaveAs Filename:=pathForFirstSave, FileFormat:=xlOpenXMLWorkbookMacroEnabled Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=pathForSecondSave, FileFormat:=xlOpenXMLWorkbook Application.DisplayAlerts = True ActiveWorkbook.SaveAs Filename:=pathForThirdSave, FileFormat:=xlOpenXMLWorkbook ActiveWorkbook.Close

Thanks so much.

 

[Domenic]

You're very welcome, cheers!