Macro, how to SaveAs a file with a new name in the same directory where the main file

[jdelano]

You could do it like this:

Sub SaveWkAs(sheetName as String)
    
    Dim folderPath As String
    Dim newFileName As String
    Dim ws as Worksheet

    Set ws = ThisWorkbook.Worksheets(sheetName)

    folderPath = Application.ActiveWorkbook.Path & "\" ' get the workbooks current file location
   
    newFileName = folderPath & ws.Range("E30").Value & " " & ws.Range("F30").Value & " " & ws.Range("G30").Value & " " & ws.Range("H30").Value & ".xlsm"    ActiveWorkbook.SaveAs Filename:=newFileName
    
    MsgBox "The file has been saved as " & newFileName
End Sub

Then when you call the Sub just pass the name of the sheet you want it to derive the file name from.

SaveWkAs("Sheet11")