VBA to save with a name based on three cell values, new file type, and user chooses location prompt

[ChrisMac1]

HI there,

I have a workbook that, as the final code to run, I want the user to receive the save as prompt, but the filename to be made up of the values in three cells of a hidden sheet.

All the user needs to choose is a location and hit save.

Any thoughts on an easy way to do this please?

Many thanks,
Chris

BTW, I am in the UK so if I don't respond immediately be mindful of my local time please.

 

[DanteAmor]

Try ths:

Sub save_based_three_cells()
  Dim cell1 As String, cell2 As String, cell3 As String
  Dim sFile As String
  
  With Sheets("Sheet5")           'Fit the name of your hidden sheet
    cell1 = .Range("B10").Value   'Fit cell1
    cell2 = .Range("C10").Value   'Fit cell2
    cell3 = .Range("D10").Value   'Fit cell3
  End With
  
  With Application.FileDialog(msoFileDialogSaveAs)
    .Title = "File Save As"
    .AllowMultiSelect = False
    .InitialFileName = cell1 & "_" & cell2 & "_" & cell3
    .FilterIndex = 1
    If .Show Then
      sFile = .SelectedItems(1)
      ActiveWorkbook.SaveAs Filename:=sFile, FileFormat:=xlOpenXMLWorkbook
    End If
  End With
End Sub

NOTE: If any of the cells have a date, then use the following, for example:

cell3 = Format(.Range("D10").Value, "YYYY-MM-DD")

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[ChrisMac1]

Try ths:

Sub save_based_three_cells()
  Dim cell1 As String, cell2 As String, cell3 As String
  Dim sFile As String
 
  With Sheets("Sheet5")           'Fit the name of your hidden sheet
    cell1 = .Range("B10").Value   'Fit cell1
    cell2 = .Range("C10").Value   'Fit cell2
    cell3 = .Range("D10").Value   'Fit cell3
  End With
 
  With Application.FileDialog(msoFileDialogSaveAs)
    .Title = "File Save As"
    .AllowMultiSelect = False
    .InitialFileName = cell1 & "_" & cell2 & "_" & cell3
    .FilterIndex = 1
    If .Show Then
      sFile = .SelectedItems(1)
      ActiveWorkbook.SaveAs Filename:=sFile, FileFormat:=xlOpenXMLWorkbook
    End If
  End With
End Sub

NOTE: If any of the cells have a date, then use the following, for example:

cell3 = Format(.Range("D10").Value, "YYYY-MM-DD")

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

DanteAmor, you're an absolute legend, thank so much for the speedy response, worked a treat... Thanks so much!!

 

[ChrisMac1]

Try ths:

Sub save_based_three_cells()
  Dim cell1 As String, cell2 As String, cell3 As String
  Dim sFile As String
 
  With Sheets("Sheet5")           'Fit the name of your hidden sheet
    cell1 = .Range("B10").Value   'Fit cell1
    cell2 = .Range("C10").Value   'Fit cell2
    cell3 = .Range("D10").Value   'Fit cell3
  End With
 
  With Application.FileDialog(msoFileDialogSaveAs)
    .Title = "File Save As"
    .AllowMultiSelect = False
    .InitialFileName = cell1 & "_" & cell2 & "_" & cell3
    .FilterIndex = 1
    If .Show Then
      sFile = .SelectedItems(1)
      ActiveWorkbook.SaveAs Filename:=sFile, FileFormat:=xlOpenXMLWorkbook
    End If
  End With
End Sub

NOTE: If any of the cells have a date, then use the following, for example:

cell3 = Format(.Range("D10").Value, "YYYY-MM-DD")

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Sorry DanteAmor, one thing, I need to keep the filetype as macro enabled, sorry, my mistake. I tried adding this but it didn't work...

ActiveWorkbook.SaveAs Filename:=sFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled

 

[DanteAmor]

Change this:

.FilterIndex = 2

ActiveWorkbook.SaveAs Filename:=sFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled

 

[ChrisMac1]

PERFECT!!! Worked a treat, thank so much again!