Excel Macro to Save a Specific Worksheet as a New File

Add to Favorites

This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be able to control which worksheet the macro should copy, the name of the new file, and where the new file should be saved.

This is great for when you have a Data tab or similar other tab that needs to be exported to a new file on a regular basis.

Sub Save_Specific_Worksheet_as_New_File()
    'The name of the sheet that will be copied to a new file
    SheetToCopy = "Sheet1"
    'The name of the new file that will be created - currently set as the same as the sheet to copy
    NewFileName = SheetToCopy
    'Puts the worksheet into its own file (workbook)
   'Saves the new Excel workbook
    ActiveWorkbook.SaveAs "D:\" & NewFileName & ".xlsx"
    'Closes the newly created workbook so you are still looking at the original workbook

End Sub

The code itself is heavily commented, so if you copy it into a module, you should see how to edit it, but, here, I'll also show you how to edit the macro to work for your needs.

To change the worksheet that will be copied to a new file, simply change Sheet1, after where it says "SheetToCopy", to the name of the worksheet that you want to save as a new file. This is the name that appears on the tab in the worksheet.

To change the name of the new file, simply change SheetToCopy after where it says "NewFileName" to whatever you want. It currently saves the new file with the name of the worksheet that will be saved. You can add extra text to this, the current date and time, whatever you want.

To change where the file is saved, change the section after where it says "ActiveWorkbook.SaveAs." D is the hard-drive letter where it will be saved, so change that to your hard-drive letter, probably C. Then, just change everything after the backslash "\" to save the file in whichever folder you want. Don't change & NewFileName & ".xlsx" because this is the part that gives the new file its name and extension.

I hope this helps! :)

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