In this post we will see how to copy paste vba code from a sheet to a different sheet using VBA code in excel.
We will use a source object and a destination object in VBA to define a code that will copy VBA code from source sheet to destination sheet using VBA code , for example from Sheet1 to Sheet2 .
Step 1
Open VB editor from Developer tab in excel as shown in the figure.
Step 2
Insert a module in which we will write a VBA code to copy vba code from one sheet to another sheet
Step 3
Now add a reference to “MS VB for Applications Extensibility 5.3” as shown in the screenshot below, that will enable us to use CodeModule object in VBA to copy VBA code from one sheet to a different sheet.
Tools->Reference-> MS VB for Applications Extensibility 5.3
Step 4
Now double click the newly created module to open that and paste the below code in the module.
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
CodePaste.AddFromString CodeCopy.Lines(1, numLines)
End Sub
Now run the code by pressing F5 or by clicking on play button in the editor to copy vba code from one sheet to another sheet in excel.
Hope this helped.
