Save the Current Worksheet as a New Excel Workbook File

Add to Favorites

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


'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


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.

How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.

Excel Forum