Over My Head... Trying to open "Saveas" and prefill name based on Concat cell.

[Brassaxe]

I am trying to have Excel open a save as and prefill the name based on a concat cell, but NOT prefill the location.
I am good with excel but the VBA is going over my head. This is what I have

Sub SaveFile()
Dim NameFile As Variant
With Worksheets("Settings")
NameFile = .Range("X2") & ".xlsm"
End With
ThisWorkbook.SaveAs FileName:=NameFile
End If
End Sub

 

[mumps]

In order to save the workbook, you need to include a location. Do you want the macro to ask you to select a location?

 

[Brassaxe]

In order to save the workbook, you need to include a location. Do you want the macro to ask you to select a location?

Do I need to define or have defined the location to input the name in the dialog? Can it just open the dialog box, prefill the name, and let me define the location from there?

I wish I knew what I was doing I tried assembling the above code from what I could find online. I'm not good at it yet.

 

[mumps]

I would suggest that the easiest way would be to first select the destination folder and then save the file based on X2. Please advise.

 

[Brassaxe]

That may become complicated as multiple users will be using this base .xlsm as the template to save it's completed form to their own defined locations. But you say you can have it prompt for a location?
???

 

[Brassaxe]

I dunno why it added those three questions marks lol

 

[mumps]

Yes, I can have it prompt for a location if that will work for you.

 

[Brassaxe]

Yeah, that would be awesome

 

[mumps]

Try:

Sub SaveFile()
    Application.ScreenUpdating = False
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            sPath = .SelectedItems(1)
            ActiveWorkbook.SaveAs FileName:=sPath & Application.PathSeparator & Sheets("Settings").Range("X2") & ".xlsm", FileFormat:=52
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[Brassaxe]

Allows the folder selection ,then is says

Run-time error '1004':
Method "SaveAs' of object '_Workbook' failed

Flags this line

ActiveWorkbook.SaveAs FileName:=sPath & Application.PathSeparator & Sheets("Settings").Range("X2") & ".xlsm", FileFormat:=52