VBA Code To Save As A File To A Selected Location

excelbytes

Active Member
Joined
Dec 11, 2014
Messages
291
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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:

VBA Code:
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi,
combined with that function you can just do this and run SaveTest:

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Solution

Forum statistics

Threads
1,223,157
Messages
6,170,420
Members
452,325
Latest member
BlahQz

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
Back
Top