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
Bookmarks