Okay this is a difficult task. The goal is to export an active worksheet with specific range of cells to a new workbook and save the file. I have stumbled across code that does what I want but does not retain the formatting I have setup and requires that I direct it to a specific file path. I do not know the file path of the client I am creating this for and so would like to save in the same file as the existing workbook. This is currently set to execute on a command button click. The code I have now works but not as well as I would like it to. There is also the matter of saving a file with macros which is what I need to do with this and the xlsm extension accomplishes this. I know this is a great forum and any help would be appreciated!

    Dim strFileName As String
    
    strFileName = InputBox("Type a name for the new workbok", "File Name")
    If Trim(strFileName) = vbNullString Then Exit Sub
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Range("A1:J40").Copy
    Sheets.Add.Range("A1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    ActiveSheet.UsedRange.EntireColumn.AutoFit
    ActiveSheet.Move
    ActiveWorkbook.SaveAs "C:\test\" & strFileName & ".xlsm", xlOpenXMLWorkbookMacroEnabled
    ActiveWorkbook.Close False
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True