Saving as Macro free workbook not completely working

[deedledoo]

Hi,

I'm trying to remove the macros from a specific worksheet. My idea was to just save it as an xlsx file then move that sheet to where I wanted it. The method works in that it removes the Module1 code, but it doesn't remove the code that I have attached to the actual worksheet, (i.e. when in VB editor, double click on the worksheet and the code in there). Does anyone know of a way to remove this code as well without having to enable the Microsoft Visual Basic for Applications Extensibility?

I've looked through lots of forums and different tips from excel sites but they either require the above reference to be enabled or say to use fileformat:=51 which doesn't remove the code from the sheet, only the module.
Here is my code;

Sub Stupid_saving()
    Application.ScreenUpdating = False
    Dim temp_wbk As Workbook
    Dim source_wbk As Workbook
   
    Set source_wbk = ActiveWorkbook
    
    Workbooks.Add
    Set temp_wbk = ActiveWorkbook
    
    source_wbk.Sheets("Activities").Copy after:=temp_wbk.Sheets(Sheets.Count)


    temp_wbk.SaveAs Filename:="W:\NOMACRO.xlsx", FileFormat:=51
    
    Application.ScreenUpdating = True
End Sub

 

[MARK858]

Are you closing and reopening the workbook after the save?

 

[deedledoo]

I wasn't but I can. I've tried opening it manually after and the code is still attached to the sheet.

 

[MARK858]

Afraid I can't help as I can't reproduce your result.

I ran your code changing your filepath to my desktop, closed NOMACRO.xlsx, opened NOMACRO.xlsx from my desktop and all the code was stripped from the file (Sheet code and regular module).

Are you sure you are not looking at the original workbooks properties rather than the NOMACRO file?

 

[Macropod]

When you save a workbook in the xlsx format, the saved file will contain no macros. However, if the workbook was already an xls or xlsm workbook containing macros, those macros will remain available until the original workbook is closed. Of course, if the macros are stored in a template, they will remain available regardless of the save format.

 

[deedledoo]

Thanks for the additional step, having closed both workbooks and then opening either one or both, the code has now been stripped the way I want it to. Now to get rid of that pesky message box saying yes I do want to save as macro free.

Thanks for your help Mark and Macropod.

 

[MARK858]

Application.DisplayAlerts = False
'save code
Application.DisplayAlerts = True

 

[deedledoo]

Thanks Mark, I tried that too and it didn't change it. The only thing that removed the code was to close both books, at least in my case anyway.

 

[MARK858]

Post the code as you tried writing it and I will see if I get a chance to look at it when I get in.

 

[MARK858]

Works fine at suppressing the message when I run it.

Sub Stupid_saving()
    Application.ScreenUpdating = False
    Dim temp_wbk As Workbook
    Dim source_wbk As Workbook

    Set source_wbk = ActiveWorkbook

    Workbooks.Add
    Set temp_wbk = ActiveWorkbook

    source_wbk.Sheets("Activities").Copy after:=temp_wbk.Sheets(Sheets.Count)

    With Application
        .DisplayAlerts = False
    temp_wbk.SaveAs Filename:="W:\NOMACRO.xlsx", FileFormat:=51
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub