Excel Macro to Save a Specific Worksheet as a New File

Add to Favorites
Author:

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






Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
Macro: This free Excel macro illustrates all of the possible parameters and arguments that yo...
Excel Macro that Searches Entire Workbook and Returns All Matches
Macro: This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workboo...
Pass Values from One Macro to Another Macro
Tutorial: How to pass variables and values to macros. This allows you to get a result from one macr...
Simple Excel Function to Extract a Word or Text from a Cell
Tutorial: Excel function that makes it easy to extract a word or text from a cell in Excel. This is ...
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...
VBA IF Statement in Excel Macros
Tutorial: IF statements allow you to make decisions in your Macros and VBA in Excel. An IF statement...


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.

Tutorial Details
Similar Content
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
Macro: This free Excel macro illustrates all of the possible parameters and arguments that yo...
Excel Macro that Searches Entire Workbook and Returns All Matches
Macro: This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workboo...
Pass Values from One Macro to Another Macro
Tutorial: How to pass variables and values to macros. This allows you to get a result from one macr...
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course