Save the Current Worksheet as a New Excel Workbook File

This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a new Excel workbook file.

Once you install the macro, simply run it when you are viewing any worksheet that you want to be in a separate file and it does everything for you.

Sub Save_Current_Worksheet_as_New_File()


'Gets the name of the currently visible worksheet

Filename = ActiveSheet.Name


'Puts the worksheet into its own workbook

ThisWorkbook.ActiveSheet.Copy


'Saves the workbook - uses the name of the worksheet as the name of the new workbook

ActiveWorkbook.SaveAs "C:\" & Filename & ".xlsx"


'Closes the newly created workbook so you are still looking at the original workbook

ActiveWorkbook.Close


End Sub

The only things you need to or might want to change are related to where you save the new file and under what name.

Where it says "C:\" simply change this to the desired location where you would like to save the file on your computer. The letter C is the name of the hard drive where you will save the file and everything after that are folder names.

To get this for your destination folder, simply navigate to it and look to the top of the window, where you should see the file path that leads you to the current directory. If you are in Windows 7 or later, click where the file path is located and you will see it change to the correct format that you can then copy and paste into this macro.

To change the file name, simply replace ActiveSheet.Name with the desired name of the file.

If you are saving a file with a file format different than the default Excel 2007 and later format of xlsx, then replace xlsx with the correct file extension, be that xls, xlsm, xml, etc.

---