Save the Current Worksheet as a New File in the Current Folder

Add to Favorites

This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from the other Save Worksheet macros because the other ones require you to specify a hard-coded location for where the new file will be saved.

Just to repeat, this macro puts the new file into the same folder as where the current file is located. This is important so you don't waste time wondering where the new Excel file is located.

Sub Save_Worksheet_as_New_File_in_Current_Folder()
    'Gets the name of the currently visible worksheet
    Filename = ActiveSheet.Name
    'Copys the visible worksheet to a new workbook
    'Saves the new Excel file
    ActiveWorkbook.SaveAs Filename & ".xlsx"
    'Closes the newly created workbook so you are still looking at the original workbook

End Sub

As usual, the code above is heavily commented and should be pretty easy to understand once you copy it into a module in your workbook.

To change the name of the new file, simply change ActiveSheet.Name to whatever you want, hard-coded text, a date or time, or a combination of all of the above. Currently, the new file will have the name of the sheet that is being used to create it.

That's about it. There's not so much to change with this macro.

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