复制工作表而不复制代码 [英] Copy a worksheet without copying the code

问题描述

我可以通过调用工作表的.Copy方法来复制工作表.

I can copy a worksheet by calling its .Copy method.

Sheets("Example").Copy After:=Worksheets("Sheet3")

但是，这也会复制与该工作表关联的所有宏或事件处理程序.如何在不复制任何Visual Basic代码的情况下复制工作表?

However, this also copies any macros or event handlers associated with that worksheet. How do I copy the worksheet without copying any Visual Basic code?

推荐答案

复制工作表后，可以按名称引用它，然后从代码模块中删除所有行:

After copying the sheet, you can reference it by name and then delete all of the lines from the code module:

Sheets("Example").Copy After:=Sheets("Sheet3")

' Get the code/object name of the new sheet...
Dim strObjectName As String
strObjectName = ActiveSheet.CodeName

' Remove all lines from its code module...
With ThisWorkbook.VBProject.VBComponents(strObjectName).CodeModule
    .DeleteLines 1, .CountOfLines
End With

要使用项目组件，您需要确保在Excel的宏设置中启用了"Trust access to the VBA project object model"选项.

To work with project components, you'll need to ensure that the "Trust access to the VBA project object model" option is enabled in Excel's macro settings.

这篇关于复制工作表而不复制代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！