将VBA代码从一个Worksheet复制到另一个使用VBA代码 [英] Copy VBA code from one Worksheet to another using VBA code
问题描述
我已经添加了一个用于应用程序可扩展性的MS VB参考5.3
我不知道从哪里开始,但这是我刚刚开始的,它不会在任何地方,也可能都是错的。请帮助 - 只需要以编程方式将表单vba代码复制到另一个表vba窗格。
Dim CodeCopy为VBIDE.CodePane
设置CodeCopy = ActiveWorkbook.VBProject.VBComponents(Sheet2)VBE
ActiveWorkbook.VBProject.VBComponenets(Sheet3)。CodeModule = CodeCopy
使用 CodeModule
对象而不是 CodePane
,那么您可以创建一个第二个变量来表示目标模块(您将在其中粘贴代码)。
code> Sub test()
Dim CodeCopy作为VBIDE.CodeModule
Dim CodePaste作为VBIDE.CodeModule
Dim numLines As Integer
设置CodeCopy = ActiveWorkbook.VBProject.VBComponents(Sheet2)。CodeModule
设置CodePaste = ActiveWorkbook.VBProject.VBComponents(Sheet3)。CodeModule
numLines = CodeCopy.CountOfLines
'使用这一行来清除可能已经在sheet3中的所有代码:
'如果代码Paste.CountOfLines> 1 Then CodePaste.DeleteLines 1,CodePaste.CountOfLines
CodePaste.AddFromString CodeCopy.Lines(1,numLines)
End Sub
除了添加引用参考MS VB应用程序可扩展性5.3
您还需要启用程式化访问VBA项目。
在Excel 2007+中,单击主功能区上的开发人员项,然后
单击代码面板中的宏安全项。在该对话框中,
选择宏设置并检查对VBA项目的信任访问
对象模型。
Ok here is what I want to accomplish: I am trying to copy all the VBA code from "Sheet2" to "Sheet 3" code pane. I'm NOT referring to copying a Module from one to another but the excel sheet object code.
I already added a Reference to MS VB for Applications Extensibility 5.3
I'm not sure where to start but this is what I have started with and its not going anywhere and probably all wrong. Please Help - Simply want to programmatically copy sheet vba code to another sheet vba pane.
Dim CodeCopy As VBIDE.CodePane
Set CodeCopy = ActiveWorkbook.VBProject.VBComponents("Sheet2").VBE
ActiveWorkbook.VBProject.VBComponenets("Sheet3").CodeModule = CodeCopy
Use the CodeModule
object instead of the CodePane
, then you can create a second variable to represent the destination module (where you will "paste" the code).
Sub test()
Dim CodeCopy As VBIDE.CodeModule
Dim CodePaste As VBIDE.CodeModule
Dim numLines As Integer
Set CodeCopy = ActiveWorkbook.VBProject.VBComponents("Sheet2").CodeModule
Set CodePaste = ActiveWorkbook.VBProject.VBComponents("Sheet3").CodeModule
numLines = CodeCopy.CountOfLines
'Use this line to erase all code that might already be in sheet3:
'If CodePaste.CountOfLines > 1 Then CodePaste.DeleteLines 1, CodePaste.CountOfLines
CodePaste.AddFromString CodeCopy.Lines(1, numLines)
End Sub
In addition to adding a reference to "Reference to MS VB for Applications Extensibility 5.3"
You'll also need to enable programmatic access to the VBA Project.
In Excel 2007+, click the Developer item on the main Ribbon and then click the Macro Security item in the Code panel. In that dialog, choose Macro Settings and check the Trust access to the VBA project object model.
这篇关于将VBA代码从一个Worksheet复制到另一个使用VBA代码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!