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

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.




Similar Content on TeachExcel
Save the Current Worksheet as a New File in the Current Folder
Macro: This Excel Macro saves the currently visible worksheet into the SAME folder as the current...
Excel Macro to Save a Specific Worksheet as a New File
Macro: This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its ...
Change the Default Number of Worksheets Created in a New Excel Workbook
Tutorial: Learn how to change the number of worksheets that are in new Excel workbooks. Excel work...
Combine Multiple Workbooks into One
Macro: This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets...
Print The Current Worksheet in Excel
Macro: This free Excel macro will print the current active worksheet in Excel. This means th...
Display the Current Day of the Week in Excel
Tutorial: Use a formula or formatting to display the current day of the week in Excel. Sections: For...



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.


Similar Content
Save the Current Worksheet as a New File in the Current Folder
Macro: This Excel Macro saves the currently visible worksheet into the SAME folder as the current...
Excel Macro to Save a Specific Worksheet as a New File
Macro: This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its ...
Change the Default Number of Worksheets Created in a New Excel Workbook
Tutorial: Learn how to change the number of worksheets that are in new Excel workbooks. Excel work...
Excel Forum