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

Save a worksheet as new workbook in current folder

0

So i tried the code which was posted on teachexcel. here is the link to that post: [link](https://www.teachexcel.com/free-excel-macros/203_save-the-current-worksheet-as-a-new-file-in-the-current-folder.html?nav=sim_side_col)

according to the code given the file should be saved in the current folder but for some reason my files keeps getting saved in "Documents"

is there any way to fix it? there is also a way to change a macro so that the file saves to a specific folder but that won't be useful in my case

Answer
Discuss

Answers

0
Selected Answer

Hi and welcome to the Forum

You could specify another default path and select where the file is saved by making the code change in bold below:

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
    ThisWorkbook.ActiveSheet.Copy

    'Saves the new Excel file but displays the dialogue
    Application.Dialogs(xlDialogSaveAs).Show "C:\Users\Public\Desktop\" & Filename & ".xlsx"

    'Closes the newly created workbook so you are still looking at the original workbook
    ActiveWorkbook.Close

End Sub

You'll need to chnage my string "C:\Users\Public\Desktop\" to a path on your computer (and remember to end it with the backslash \).

Hope this helps.

Discuss
0

"Current" is a sometimes ambiguous term, but if you want to save it in the same driectory as the file where the code is, you can use this:

ThisWorkbook.Path

After this, you will still need to add the trailing slash \ and the file name and extension to save the file.

Discuss

Discussion

Good point, Don. Much more reliable
John_Ru (rep: 3677) Jul 2, '22 at 2:07 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login