CODEWITHSUNDEEP
vbaexcelmacros
Beth
Beth

Reputation: 23

Disarming certain macros when saving a copy of a workbook

I have set up a macro that creates and saves a copy of an original workbook. However I do not want certain macros from the original to run on the copy. Is there some way that I can get the macro, that creates and saves a copy of the original, to 'disarm' certain macros?

Thanks in advance for your help!

Upvotes: 0

Views: 69

Answers (2)

user3598756
user3598756

Reputation: 29421

you could use the VBIDE object library

disclaimer: carefully read the "Introduction" and its CAUTION clause

then add your project the needed reference to Microsoft Visual Basic For Applications Extensibility 5.3. and code as follows: 

Sub DeleteModule(wb As Workbook, moduleName As String)
    With wb.VBProject
        .VBComponents.Remove .VBComponents(moduleName)
    End With
End Sub

to be called by your main sub as follows

Sub main()

    ' ... code to get to the original workbook saved copy
    ThisWorkbook.SaveAs "C:\Users\...\FileTest.xlsm"

    DeleteModule ActiveWorkbook, "ModuleNameToDelete" '<--| this will erase the wanted module from the active workbook (i.e. the just "savedas" workbook)

    '... more code

End Sub

Upvotes: 1

Jason
Jason

Reputation: 186

This is dependent upon the workbook design. First, are the macros tied to buttons? If yes, then they can be removed from the buttons, or the buttons could be deleted. If not, there is no direct way to 'disarm' macros as you put it. One trick that comes to mind however is to make the macros you want to disable dependent on the name of the workbook (assuming the saved workbook is under a different name). Then, the macros can be set to run only under the original workbook name using if statements.

Upvotes: 0

Related Questions