Excel VBA Save As

[shapeshiftingkiwi]

I'm trying to create a save as button in excel and adapted the below code from a video guide. However whenever I click on the button or hit the code to run, nothing happens. The macro is linked to the button and the button does "depress" to show it was clicked. What am I missing?

Sub Save_As()
With Application.FileDialog(msoFileDialogSaveAs)
.Title = "Save"
.ButtonName = "Save Form"
.InitialFileName = "C:\Users\MYNAME\OneDrive - MY COMPANY\Documents\Excel\" & Range("M3").Value
End With
End Sub

 

[iggydarsa]

Sub Save_As()
With Application.FileDialog(msoFileDialogSaveAs)
.Title = "Save"
.ButtonName = "Save Form"
.InitialFileName = "C:\Users\MYNAME\OneDrive - MY COMPANY\Documents\Excel\" & Range("M3").Value
.Show
End With
End Sub

 

[shapeshiftingkiwi]

Hmm, so that now brings the pop up up but when it's saved it doesn't actually seem to appear in that pathway. I found another method. Below the Save_As sub correctly saves the sheet but there's no pop up (which I want) and the Save_As2 sub has the pop up but it doesn't actually save. Any idea how to get the pop up and get it to save?

Sub Save_As()
Dim path As String
Dim filename1 As String

path = "P:\COMPANY - Production\Production Form Save As Test\" & Range("Save_As!M3").Value
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=path & filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbook

End Sub
___________________________
Sub Save_As2()
With Application.FileDialog(msoFileDialogSaveAs)
.Title = "Save"
.ButtonName = "Save Form"
.InitialFileName = "P:\COMPANY - Production\Production Form Save As Test\" & Range("Save_As!M3").Value
.Show
End With

End Sub

 

[iggydarsa]

check this example out and see if this will help

 

[shapeshiftingkiwi]

Hmm I still can't figure it out. The answer may be in there but my knowledge isn't enough to spot it and adapt it to my code. Any ideas? This code successfully saves it in the right spot but it doesn't have to pop up showing where it's going to save.

Sub Save_As()
Dim path As String
Dim filename1 As String

path = "P:\COMPANY - Production\Production Form Save As Test\" & Range("Save_As!M3").Value
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=path & filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbook

End Sub

This has the pop up showing the folder it's going to save in and what it's going to be called but it doesn't actually save when I click save.

Sub Save_As2()
With Application.FileDialog(msoFileDialogSaveAs)
.Title = "Save"
.ButtonName = "Save Form"
.InitialFileName = "P:\COMPANY - Production\Production Form Save As Test\" & Range("Save_As!M3").Value
.Show
End With
End Sub

 

[shapeshiftingkiwi]

I have this code that works great but after I click "Save Form" and go to the pathway it was supposed to save, it's not there. How can I get this to actually save?

Sub Save_As2()
With Application.FileDialog(msoFileDialogSaveAs)
.Title = "Save"
.ButtonName = "Save Form"
.InitialFileName = "P:\COMPANY NAME - Production\Production Form Save As Test\" & Range("Save_As!M3").Value
.Show
End With
End Sub

 

[Micron]

There's nothing in that code that saves the file. The dialog button does not perform that function - you need to use the Execute method of the dialog. I've never used that dialog but I think you'd need

With Application.FileDialog(msoFileDialogSaveAs)
   .Title = "Save"
   .ButtonName = "Save Form"
   .InitialFileName = "P:\COMPANY NAME - Production\Production Form Save As Test\" & Range("Save_As!M3").Value
   If .Show Then
      .Execute
   End If 'or Else or ElseIf might go here
End With

 

[Fluff]

Try adding .Execute before the End With

 

[shapeshiftingkiwi]

Thanks Micron, that works!

 

[shapeshiftingkiwi]

Ended up using this.


With Application.FileDialog(msoFileDialogSaveAs)
.Title = "Save"
.ButtonName = "Save Form"
.InitialFileName = "P:\COMPANY NAME - Production\Production Form Save As Test\" & Range("Save_As!M3").Value
If .Show Then
.Execute
End If 'or Else or ElseIf might go here
End With