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