Excel VBA Macro: Save As Dialog (Allow User to Select Saved File Location)

Excel VBA Macro: Save As Dialog (Allow User to Select Saved File Location). In this video, we go over how to create a macro that allows a user to select a location to save a particular file. We write code that, when run, will prompt the user to select a save file name and location for a workbook (created from copying the activesheet), and then the save is executed.

Code (YouTube doesn't allow brackets; so LT and GT are used for less than and greater than, respectively):

Sub allow_user_to_select_save_location()

Dim region As String
Dim selection As Variant

region = ActiveSheet.Cells(2, 2).Text
ActiveSheet.Copy

selection = Application.GetSaveAsFilename( _
FileFilter:="Excel Workbook (*.xlsx), *.xlsx", _
Title:="Please Select Location to Save File", _
InitialFileName:="Region Report " & region & " " & _
Format(Date, "mm-dd-yy"))

If selection LTGT False Then
ActiveWorkbook.SaveAs Filename:=selection
End If

ActiveWorkbook.Close savechanges:=False

End Sub

Data used in this video:

#ExcelVBA #ExcelMacro

[francinepowell]

Good Video! Appreciated the slower pace by typing out the code and them demo the results; then making changes and demo the changes. This is what I was looking for allow the user to get a copy of the sheet, but he can select the filename and path.

[lorielange]

Huge help, exactly what I was looking for and needed!

[russf]

Fantastic, so helpful, thanks. Had created code to clear a weekly timesheet at start of new week but wanted to add the option to save a copy before doing so. Just a few minor tweaks and voila... 😊

[makubexho]

@greggowaffles fantastic video as usual - just a question though - I used your codes and everything seems working until i receive a prompt, immediately after i select a location and hit Save, saying " a file named 'FALSE.xlsx' already exists in this location. Do you want to replace it". My feeling it has something to do with the if-statement or i could be wrong. Any suggestion?

[dlee]

Greg, That was awesome, you went at a good pace and it was very clear. Can you do the same thing but create a folder rather than a workbook?

[mizanranawat]

File not found in the path where I have saved... please help

[pugu]

Excuse me sir..
i want to ask about the Formulas..

how to prevent formulas from being carried over to the workbook, or not link to master workbook..

thank you before ..

[arulmozhikarunanidhi]

Vba will have scope in future bcoz I want to switch my career towards vba

[PlayfulPranksters]

the file is not found after saving huhu