Microsoft Office Forums - View Single Post - How to save Excel file by field name as filename

jolivanes · 01-21-2017, 04:02 PM

I noticed that both the Customer Number and Request Number are in Sheet1 in Cells(4, 2) = B4 and in Cells(2, 5) = E2
If you change that, you need to change these references also.

Code:

Sub Maybe()
    Dim wb1 As Workbook, wbName As String, j As Long
    Application.ScreenUpdating = False
    Set wb1 = ThisWorkbook
    wbName = wb1.Sheets("Sheet1").Cells(4, 2).Value & " " & wb1.Sheets("Sheet1").Cells(2, 5).Value
    ActiveWorkbook.SaveAs Filename:=CreateObject("WScript.Shell").Specialfolders("Desktop") & "\" & wbName, _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled
    ActiveWorkbook.Close
  Application.ScreenUpdating = True
End Sub