Excel Macro to Save a Specific Worksheet as a New File

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)
    ThisWorkbook.Sheets(SheetToCopy).Copy
   
   'Saves the new Excel workbook
    ActiveWorkbook.SaveAs "D:\" & NewFileName & ".xlsx"
    
    'Closes the newly created workbook so you are still looking at the original workbook
    ActiveWorkbook.Close

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! :)

---