Create a new workbook with no Macros

[ziadh15]

Hello

I have finished writing my code.
After hitting a command button, it will select all visible sheets except two that I specified; and will create a new workbook with those selected sheets.

Now, in that new workbook, when I try to save it; I get an error saying:
" The following features cannot be saved in macro-free workbooks: To save a file with these features, click No and then choose a macro -enabled file type in the File Type List. To continue saving as a macro free workbook click yes"

1) If I click "yes" I get an another error " Errors were detected while saving the file. Microsoft Excel may able to save the file be removing some features. To make repairs click continue. To cancel click cancel"

2) If I click "No", It will force me to chose a file type. After trial and error, I found out that "Excel 97-2003.xls" is the type that lets it save.

My question; what is a workaround to avoid all this mess. I just want the user to be able to save it right away.
Maybe force the VBA to create the new workbook in that specific file type? Or maybe create the new workbook with no Macros?

Appreciate your help
Thanks

Private Sub CommandButton1_Click()
 


Dim ws As Worksheet
Dim wkb As Workbook
Set wkb = ActiveWorkbook


For Each ws In Sheets
If ws.Name = "Start" Then ws.Visible = False
If ws.Name = "Revision Log" Then ws.Visible = False
If ws.Visible Then ws.Select (False)
Next

ActiveWorkbook.Sheets.Copy

wkb.Close False

End Sub

 

[Roderick_E]

check the sheets and see if there is code on them. On the original workbook, right click the tabs and VIEW CODE. Is there code there?

 

[ziadh15]

No codes on any of the sheets.

 

[Roderick_E]

Hmm... strange You could save the new workbooks as macro-enabled and then it wouldn't complain, though there is nothing obvious that indicates your new sheets have macros...UNLESS you are using a function? UDF or something?

 

[ziadh15]

When it creates the new workbook, I noticed that my main sheet is inside that workbook but hidden.
It doesn't include any code except the following

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Could that be the problem?

Anyway I force the new excel workbook to open in a specific template that will allow user to save with no warning messages?

 

[Roderick_E]

Yes, delete that code (it's not doing anything anyhow) and hopefully the problem will go away. Saving files in different format: Use VBA SaveAs in Excel 2007-2016