0

I am trying to write a macro to save an excel file in a defined location, using a value in a cell as the title in the format .xlsm, it appears to work but does not actually save the file? i am not sure what i have done wrong? here is the macro:

Sub Savefileas()
ThisFile = Range("B4").Value
Dim varResult As Variant

varResult = Application.GetSaveAsFilename(FileFilter:= _
"Macro Enabled Workbook" & "(*.xlsm), *xlsm", Title:=Range("B4").Value &".xlsm", _
InitialFileName:="C:\Work\" & ThisFile & ".xlsm")

End Sub

Thanks in adavce

R3uK
  • 14,417
  • 7
  • 43
  • 77
Maxe1984
  • 37
  • 6

1 Answers1

0

Give this a try. Error catching has also been added.

Sub Savefileas()
    Dim ThisFile As String
    Dim varResult As Variant
    ThisFile = Range("B4").Value
    varResult = Application.GetSaveAsFilename(FileFilter:= _
    "Macro Enabled Workbook" & "(*.xlsm), *xlsm", Title:=ThisFile & ".xlsm", InitialFileName:="C:\My Documents\" & ThisFile & ".xlsm")
    With ActiveWorkbook
        On Error GoTo message
        .SaveAs varResult & ".xlsm", FileFormat:=52
        Exit Sub
        message:
        MsgBox "There is an error"
    End With
End Sub
Brad
  • 1,450
  • 2
  • 16
  • 37
  • This has saved the file in the correct format, however using the previous macro i had the save as dialog box appear and this would give the user the ability to select another folder within the file structure. The idea is the spreadsheet is used for a variety of different projects once the user presses save they can select the correct project folder before finalizing the save. I hope i have made that clear – Maxe1984 Jan 17 '17 at 15:06
  • @Maxe1984 I've updated my answer. Is this what you are looking for? – Brad Jan 17 '17 at 15:29
  • It doesnt seem to be grabbing the name from cell B4 only using the current work sheet name?? Also this is saving to last current folder, is there a way of specifying a starting location?? – Maxe1984 Jan 17 '17 at 15:30
  • @Maxe1984 I have added both cell B4 as file name functionality and default directory functionality. To change the default directory, change the "C:\My Documents\" string for InitialFileName. – Brad Jan 17 '17 at 15:39