Saving Worksheet via VBA

[dave8]

I have a Workbook with several Worksheets. Each worksheet has some VBA code. In one of the worksheet, I copy the worksheet to a new workbook (book2) and save, giving it a new name. The code looks something like this:

With DestWB
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
....
End With

This file extension on this is .xlsx, but there is a message that pops up to indicate it cannot save with this file extension because of macros. I can click 'Yes' to continue saving as a macro-free workbook.

Is there a way to strip the macros when the worksheet is copied? And, is there a way to by-pass prompting and automatically save as macro-free workbook?

 

[JoeMo]

Not sure of this but try:

With DestWB
Application.DisplayAlerts = false
    .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
    ....
  End With
Application.DisplayAlerts = True
.....

If it works to suppress the alert, Excel will strip the macros for you.

 

[fibonacci1101]

DisplayAlerts = False
[your code]
DisplayAlerts = True

 

[dave8]

DisplayAlerts = False
[your code]
DisplayAlerts = True

Thank you.