CODEWITHSUNDEEP
excelvba
Howard Foo
Howard Foo

Reputation: 11

Save with specified name from the specified cell

Hello so I used the below coding to try to "save as" the active worksheet to the current same folder, however the problem I am facing is that the file name does not appear as E6 however it is just a blank.

Also, is there a faster way to actually just omit the save as dialog and just save as a new workbook in the same folder as the macro? With the same file type as xls. Thank you.

enter image description here

enter image description here

Sub Button1_Click()

Dim varResult As Variant
Dim dirPath, fileName As String


dirPath = Application.ActiveWorkbook.Path
fileName = ActiveSheet.Range("E6").Value 'ActiveSheet.Name


Application.ScreenUpdating = False
Application.DisplayAlerts = False


varResult = Application.GetSaveAsFilename(FileFilter:= _
                 "Excel Files (*.xls), *.xls", Title:="Save As", _
                InitialFileName:=dirPath & "\" & fileName)

ActiveWorkbook.SaveCopyAs fileName:=varResult

Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub

Upvotes: 0

Views: 296

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

To directly save without using the dialog, try the next code, please:

Sub testSaveAs()
   Dim wb As Workbook
    Set wb = ActiveWorkbook 'Use here your workbook
    wb.SaveAs fileName:=ThisWorkbook.path & "\" & ActiveSheet.Range("E6").value & ".xls"
 End Sub

Upvotes: 1

Related Questions