VBA Userform Save Copy of Workbook

[Ottsel]

I'm using a userform that has an option to Save a copy of the current workbook, but to give the user the option to hit save in the window themselves.

I don't have much, but in my UserForm I was going with this approach:

Private Sub Export_Click()
Dim Path As String
Path = "G:\MyFolder\Prior_Months"

    ChDir Path
    ActiveWorkbook.SaveAs Filename:="G:\MyFolder\Prior_Months", FileFormat:=xlOpenXMLWorkbook

End Sub

I also have 2 ComboBoxes that lists the Month and the Year, so I'd like to have it prefill the Filename with:
"Name of File " & ComboBox_Month & " " & ComboBox_Year
Resulting in: "Name of File April 2023.xlsm"

But, I'm struggling to figure out the correctly way go about it.

Any tips, tricks or advice would be greatly appreciated!

 

[DanteAmor]

Hello @Ottsel.

Try this:

Private Sub Export_Click()
  Dim Path As String
  Path = "G:\MyFolder\Prior_Months"

  ChDir Path
  ActiveWorkbook.SaveAs Filename:="Name of File " & ComboBox_Month & " " & ComboBox_Year, FileFormat:=xlOpenXMLWorkbook
End Sub

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

 

[Ottsel]

Hello @Ottsel.

Try this:

Private Sub Export_Click()
  Dim Path As String
  Path = "G:\MyFolder\Prior_Months"

  ChDir Path
  ActiveWorkbook.SaveAs Filename:="Name of File " & ComboBox_Month & " " & ComboBox_Year, FileFormat:=xlOpenXMLWorkbook
End Sub

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

Hi Dante,

The name setup worked perfectly, but it didn't give me the window, but rather just saved instantly. Any ideas?

 

[DanteAmor]

but it didn't give me the window

Do you want the window to select the folder and file name?

Try this:

Private Sub Export_Click()
  Dim sPath As String, sName As String
  Dim FileSaveName As Variant
  
  sPath = "G:\MyFolder\Prior_Months"
  sName = "Name of File " & ComboBox_Month & " " & ComboBox_Year

  FileSaveName = Application.GetSaveAsFilename(InitialFileName:=sPath & "\" & sName, _
    filefilter:="Excel Files(*.xlsx),*.xlsx", Title:="Please save the file")
  If FileSaveName = False Then Exit Sub

  ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=xlOpenXMLWorkbook
End Sub

I hope it is what you need.

 

[Ottsel]

Do you want the window to select the folder and file name?

Try this:

Private Sub Export_Click()
  Dim sPath As String, sName As String
  Dim FileSaveName As Variant
 
  sPath = "G:\MyFolder\Prior_Months"
  sName = "Name of File " & ComboBox_Month & " " & ComboBox_Year

  FileSaveName = Application.GetSaveAsFilename(InitialFileName:=sPath & "\" & sName, _
    filefilter:="Excel Files(*.xlsx),*.xlsx", Title:="Please save the file")
  If FileSaveName = False Then Exit Sub

  ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=xlOpenXMLWorkbook
End Sub

I hope it is what you need.

That worked perfectly! Thank you Dante!