How to get the save file box to open when running this macro

**barman** · 11-07-2021, 06:08 PM

Hi...

I have a macro that I run to create a new spreadsheet with some data from my workbook. It works great, however it doesn't give me the option to pick the location where I want to save the spreadsheet to. Here is the current code...

Please Login or Register  to view this content.

What I would like to change is when the macro runs it first opens the save file box so that I can select what location on my computer that I want to save the Excel file to instead of defaulting to the desktop. I think I need
to add something like below that is in another macro that does the same thing except for PDF's and it opens the save file box.

Please Login or Register  to view this content.

Just not sure what to change or how to add it to the first code. Any help pointing me in the right direction would be appreciated.

Thanks!

**6StringJazzer** · 11-07-2021, 06:15 PM

I have done this before but haven't tested this exact code. If it doesn't work, be specific about what doesn't work.

Please Login or Register  to view this content.

**barman** · 11-07-2021, 08:14 PM

Hey 6StringJazzer... Thank you!

It's almost perfect. The only thing that seems odd... is a blank spreadsheet opens up in front of my workbook and then the save dialogue box opens in front of the blank spreadsheet. Once I click save the blank spreadsheet closes along with the dialogue box. Not sure if that is what is supposed to happen or if there is a way to only have the dialogue box open and not have the blank spreadsheet open also?

Also notice that if you click "Cancel" on the dialogue box it still saves the spreadsheet with the name FALSE.

Thanks again!

**6StringJazzer** · 11-07-2021, 09:40 PM

I'm not sure why you would see a blank spreadsheet. I have not seen that happen. If you attach your actual file I can try it.

Make this change to deal with the Cancel scenario:

Please Login or Register  to view this content.

**barman** · 11-08-2021, 01:44 PM

I played around with it and after moving somethings around it now works perfect. Adding the screen updating seems to be what stopped the sheet from opening. Thank you again for the help! Here is the final code I ended up with...

Please Login or Register  to view this content.

How to get the save file box to open when running this macro

LinkBack

Thread Tools

Rate This Thread

Display

How to get the save file box to open when running this macro

Re: How to get the save file box to open when running this macro

Re: How to get the save file box to open when running this macro

Re: How to get the save file box to open when running this macro

Re: How to get the save file box to open when running this macro

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Excel file won't save after running macro

VBA macro not running with excel file open

[SOLVED] VBA Open File/Run Macro/Close &Save/Open Next File

Macro needed to open "File Save As" and change save file type

Running Macro to open other worksheets then save as .csv

Running a macro on file open

How to stop getting the file save box when running a macro

Bookmarks

Bookmarks

Posting Permissions