Save a file in multiple locations

alang84

New Member
Joined
Oct 21, 2022
Messages
22
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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”

AHTW5s3Z8mCQYO_Ujb4t4rpVoByoJtSwpl5TOKOB3xApjsO53zMAzWqvQvUPkZMP6cvalcDBJrHU3Iuhhio97F2-gvGbDZVLUpGP4j7MFoMIPIDGe8LgUSfSGA5TaziOsTXqtBI911gNN_IrDtmFZuY0MLBkxKP9i99yiT19xXDyz-BGgI9frKEpE2A1k_RutNjTL7LKo1Z9IGVTtdKQnDO_76NNmflAViGNbXugwHW5VFjd3f287dqBBnEJPUxMV3P-rPyRBH1L0Athn1EhOZWbnNdbDvXoy5WzkcFv5hMo3XzUnd5Iw9NeUyul4pYfA67hBJ8YRZYa-ZHYEqUBbULFdnZI9qEKRKLDYDFuaq7WS9BNbzcVH98VlyXQm8rtyY5uOj6Pn-MB78agKLXqvWUcvzxL6lAqsv1tFK8lDHacpVfLGVFDbk4azQB9qW0pW3CJpLqeqX1QMDgkI5GC0QzPcfIxzvPLqJifPnPEW-Ji_iJg2wwhSR9EhYvNNyAQhtup1_SBjUl6tvDjxkHYXIsAYyAjL7VLVXhWWKvV3_Z1PSuztfgUljMkqWSRAeJxqnu0It8ffP3TJW9uiIH3RFOyzfUaI9hKWGTPdFFhw_cuZb06aCPBEs3gT9x4Bc8MBOc6dcD_ReONOR5agGc2ANKvpemQh3uccKKLIZDXqfln20RMe8VzmT0fH67VJcermFLzfWNcZOmL1kei-X-TTosxdhW_R-v8jUFB_L-SAv-FlMGJGFNj5rygTXtj1tBEMX63orr-ftUYmpe93epnwkxoUUItDmV2gNMX3w-wVP8lxFD00MhiV7tbTbsDoGeUdKehrogXL4X564Mo33YgnSB3Tj6hhPrn-xrbp6QJTj35MIyHAHhQ3lndCKiBohyRDuwyOlOIyujtr684xmMnkfjWapuq5K55z9PLq4EUOpsys_Y8Yd8mnh26xahoD6CEZdCmRucU-HXeXb3lBNqHWzhFkZ1jvJkxi7Zl7TKPozNe6m7oXEMOjl2BUWP0osZNFNFf5pszUy1PFJVzPKtzKZo=s0-l75-ft


AHTW5s3Ph9WVcAPXhkj63yr4k7g4FgRxLVOD8wHNKhG4xG47IrwcXD4Tkjm9zusY42apUjHVWVKUcAows4aNSle4wS1fyvQCBCzz0eqLY5w4v_N8gddkpuTah-mWX9V2guFqBZviv4yXQ8bTKB1aj5XTeYjYWZoAJy3ncyMV5AAjsENraWX5C281aLmeJ4TjSCssnbet6t10WqpjJdRJ60d_zpV-RTasZfyL9Ym8iQSRNW9jJEJNlPaiTjtkCv4Cyg-El-XQVRnnhvG3gByc_URHGR3qlYe-jbhpUQpeMtXFs-eHTcHMyfKMVC_k7gZ0qgIlS4aUaWDtqhjBXek9LvLJJCYQD9vamMniFg_JCf5MPtLqFGD-iT7QS6GcOc_jg8hHiJZwpjNTbx0YLYKrnXbS3yXgFsWTefd5oG6575e0_sy6CzeabfBEQhUFT7xNOXA3_lB0neX1IyIMpIntcvT4i70fkWPleGM-i6FJTKie7s6qoIAMPHRsoLJo20vCTDjKIZGp58Q3uzuULBaYM_duUOkfJREBXr0aVmYOTTeDn2iefp7f7cyJhUfFhI8PYg63MC8clOcqrItvDVW2nNghKY9jv2XJ8nlfCMp0r6PTaLZ6n_-YsFxQxkPGn0Tin3W81YyJRc1FSVbnXANcOWEnTfktmnlHuzwE7-33E6mccxNNJCYx88xtKCgcZG0odoim1kowDrF1e_2GaqGzovslazhcHK_h6_2e6edSfqKAB-IB9RRg28iGmbIC-bNpWtcJ6K6lNzcDx1sL4SSe-6s_y08wepShq-firuZKF38dWB82fOwxqwwG_HdE_rputl518fUVbXOC9tN713T6yboJazvSQd6I26zGCyX6leKLCvgOQIhw_BnE-jLnYUU24enwpgQNxsG0eLTGP_-xuKYE53AkEiomXCuphXkRepTHFLDDvbpDzrV8zQDtXXb-f34cib3UjQCA_Iqmm9cNGaw4LkM_0l2D4lLD5zB35DBcRT1iWbsS6SKY7M4qW3rlO8E0zI97rLXx5JkD8ALpyXs=s0-l75-ft
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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...

VBA Code:
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!
 
Upvote 0
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...

VBA Code:
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?
 
Upvote 0
You can open a copy of your original workbook before you start saving, and then close the workbook once finished...

VBA Code:
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!
 
Upvote 0
By the way, if the your original workbook is running the macro, you can refer to ThisWorkbook instead of ActiveWorkbook prior to saving...

VBA Code:
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!
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top