excel - Copy VBA code from one Worksheet to another using VBA code

Question

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

Answer 1

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.