When you manual copy a worksheet or worksheets from a xlsm workbook (with
code) with macro's disabled like this.
1) Right click on a sheet tab
2) Choose "Move or Copy"
3) Change the "To Book" to (new book)
4) Check the "Create a Copy" check box
You will see this security dialog
Manual this is no big problem but what if you have code in your
personal.xlsb or add-in that copy a sheet or sheets to a new workbook. The
dialog only popup when you try to copy a sheet or sheets from a xlsm
workbook (with code in it) with macro's disabled. Because there is no way to
check if macros are enabled or not we must always use a workaround.
Here is a basic example to save the ActiveSheet in a new workbook with a date/time stamp in your Application.DefaultFilePath. This test macro exit the sub if your answer is NO in the security dialog
See the Tip below the macro about the SendKeys line.
Sub Copy_ActiveSheet_To_New_Workbook() 'Working in 97-2013 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy sheet to a new workbook ActiveSheet.Copy Set Destwb = ActiveWorkbook 'Determine the Excel version and file extension/format With Destwb If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007 or higher 'We exit the sub when your answer is NO in the security dialog that you 'see when you copy a sheet from a xlsm file with macro's disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "Your answer is NO in the security dialog" Exit Sub Else ' Note: if you are not sure if the sheet have VBA code then always ' save in xlsm or xlsb. it will delete the code if you save as xlsx FileExtStr = ".xlsm": FileFormatNum = 52 End If End If End With ' 'Change all cells in the worksheet to values if you want ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False 'Save the new workbook and close it TempFilePath = Application.DefaultFilePath & "/" TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum .Close False End With MsgBox "You can find the file in " & TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub
Remember that SendKeys is not always reliable but Above
ActiveSheet.Copy you can try to add this code line
Application.SendKeys "y" to close the Security notice dialog with
Save in a different format
You can save also in a different format if you want
In the 97-2003 and the 2007-2013 part you can use also this to save as txt, prn, csv
FileExtStr = ".txt": FileFormatNum = -4158
FileExtStr = ".prn": FileFormatNum = 36
FileExtStr = ".csv": FileFormatNum = 6
In the 2007-2013 part you can also use
FileExtStr = ".xlsx": FileFormatNum = 51
FileExtStr = ".xlsb": FileFormatNum = 50
FileExtStr = ".xls": FileFormatNum = 56
51 = xlOpenXMLWorkbook (without macro's in 2007 and up, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007 and up, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007 and up with or without macro’s, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007 and up, xls)