Chose Where to Save

justjaxi

New Member
Joined
Apr 30, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
I have a workbook that needs a change in the final step (ActiveWorkbook.SaveAs Filename:=).

Currently the final worksheet is being saved to a specific folder (Compiled OUTPUT).

I need to change it so it prompts us to select where to save it, but with a specific file name (Quarterly Compiled_Qx_YYYY.xlsx).

I want it to be able to be saved to someone’s desktop if they chose, or any other folder they want, but maintain the file name of Quarterly Compiled_Qx_YYYY.xlsx.

How can I make it save the newly created spreadsheet to open a window to force a save directory selection but with the specific file name?

Can I simply change the string? (Drive:\Compiled OUTPUT\Quarterly Compiled_Qx_YYYY.xlsx)

Thank you!
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Try:
VBA Code:
Sub test()
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            sPath = .SelectedItems(1)
            ActiveWorkbook.SaveAs Filename:=sPath & Application.PathSeparator & "Quarterly Compiled_Qx_YYYY.xlsx"
            Cancel = True
        End If
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top