VBA Code To Save As A File To A Selected Location

[excelbytes]

I have a macro that prints out various worksheets, then saves the file based on a name in a cell on one of the worksheets. What I want to be able to do is have the file manager window pop up so the user can choose the location for the file to be saved based on the cell name located on one of the worksheets.

The line of code to save it as the name in a certain cell is:

ThisWorkbook.SaveCopyAs Sheet6.Range("A25").Value

The formula in cell A25 is:

="ICFC"&" Offering For "&TEXT(D25,"mm dd yyyy hh mm")&".xlsm"

Which in this case would result in:

ICFC Offering For 11 25 2022 16 38.xlsm

I found this code that will bring up the file manager and allow the user to select a location:

Function GetFolder() As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    '.InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function

I need to merge these so that once they choose the location the file will be saved as the name in cell A25 and the original file will be left in tact.

Thanks so much in advance for your help.

 

[JumboCactuar]

Hi,
combined with that function you can just do this and run SaveTest:

Function GetFolder() As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    '.InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem

Set fldr = Nothing
End Function


Sub SaveTest()
    saveFolder = GetFolder
  
    ThisWorkbook.SaveCopyAs saveFolder & "\" & Sheet6.Range("A25").Value
End Sub

 

[excelbytes]

Sorry, I'm not sure how this is supposed to work (I'm a novice at VBA). As I mentioned earlier, the code I have first prints several sheets then asks the user with a message box if they are done. When they say yes, I want the folder to pop up so they can choose a path and have it automatically save the file as the name in cell A25.

 

[JumboCactuar]

Sorry, I'm not sure how this is supposed to work (I'm a novice at VBA). As I mentioned earlier, the code I have first prints several sheets then asks the user with a message box if they are done. When they say yes, I want the folder to pop up so they can choose a path and have it automatically save the file as the name in cell A25.

Ok so if you copy the code i posted into a blank VBA module
then in your code, if yes is selected call the macro SaveTest

It will trigger the GetFolder function and the select folder will be passed as a string to the save copy

Try this as an example
SaveCopy.xlsm

 

[excelbytes]

I think it's working just fine, thanks!