Microsoft Office Tutorials and References
In Depth Information
Copying a Workbook with Macros Removed
The event macros are commented out. If they are used, the code needs to be
placed in the worksheet where the scroll area is to be limited, and the
apostrophes need to be removed.
Copying a Workbook with Macros Removed
This macro shows how to make a copy of the active workbook with the VBA
code removed, including the removal of UserForms and modules that may exist
in them.
Example file:
Scenario: You have a workbook with macros that you use
to update the data and to create charts or other reports that
must be sent to people outside of the company, but your
company policy does not permit revealing how data was
calculated. This macro creates a copy of the active workbook,
but without all the macros. Any UserForms and modules that
exist in the workbook are removed as well.
When using the VBComponents collection of the VBProject object of the
workbook, we loop through each item, and, depending on the type of the
component, we either delete the contents or remove the item altogether.
View the Appendix to learn how to store this procedure
in a Standard module.
Option Explicit¶
' * * * * *¶
Sub CopyWorkbookWithoutMacros()¶
'Copies the active workbook and removes any macros that¶
'variable declaration¶
Dim Ans As Variant¶
Dim WB As Workbook¶
Dim VBC As Object¶
'Ask the user for a place to save the current file¶
Ans = Application.GetSaveAsFilename( _¶
InitialFileName:="Copy of " & ThisWorkbook.Name, _¶
FileFilter:="Microsoft Excel Workbook (*.xls),*.xls")¶
If TypeName(Ans) = "Boolean" Then¶
'User pressed Cancel, exit¶
Exit Sub¶
End If¶
'Make sure access to the VBA Project exists¶
Search JabSto ::

Custom Search