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