VBA to save a workbook without the macros

OTPM · #1 01-15-2014, 12:25 AM

Hi Folks
I need some help with VBA to save a copy of a macro enabled template without the AutoOpen macro. Basically when I try and open the copy workbook it still contains the Auto_Open macro which runs. I dont want the copy to contain the AutoOpen macro.

Any help would be appreciated. Here is a copy of the code:

Code:

Sub Workbook_Open()
'
' Macro1 Macro
'
'
Dim wbName
Application.DisplayAlerts = False
On Error Resume Next
    Range("A1").Select
    ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
        False
    wbName = Range("A2").Value
    With Selection
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("A:A").ColumnWidth = 14.43
    Columns("D:D").ColumnWidth = 34
    Columns("E:K").EntireColumn.AutoFit
    Columns("D:D").Select
    With Selection
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1").Select
    ChDir "D:\Task List Templates\Task Lists\"
    
    ActiveWorkbook.SaveAs Filename:="D:\Task List Templates\Task Lists\" & wbName & ".xls", FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    
    Application.DisplayAlerts = True
    ActiveWorkbook.Close savechanges:=False
End Sub

BobBridges · #2 01-15-2014, 12:42 PM

The simplest way, I should think, would be to go to that module and put an "Exit Sub" statement immediately after the Sub statement. That way you keep the code (in case you want to refer to it later), but it doesn't do anything.

Another way would be to remove the module entirely; you can "export" it at the same time, so you'll have the code around if you need it again.

You could, of course, change your security settings so that Excel refuses to run the autorun macro without your permission. But I don't think you can make that setting specific to one workbook, so Excel would ask you about macros on every .xlsm, every time you opened one, so maybe that doesn't appeal to you.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
the saveas macro in excel 2010 is not transferring the macros to the new workbook	missmr28	Excel Programming	3	06-20-2012 05:30 AM
#REF! error when opening a workbook that contain a refference to another workbook	tanababa	Excel	2	06-07-2012 03:11 PM
Range(Cell1,Cell2) Error on another workbook controlling some other workbook?	tinfanide	Excel Programming	1	02-09-2012 04:08 PM
macro to transfer data from one workbook to another workbook	virsojour	Excel Programming	5	02-01-2011 08:58 PM
Select a range in one one workbook while working in other workbook	Slow&Steady	Excel	1	02-21-2010 03:34 AM