Paste VBA Code From One Sheet To A Different Sheet

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.

Open developer tab to write VBA to copy code from one sheet to a different sheet

Step 2

Insert a module in which we will write a VBA code to copy vba code from one sheet to another sheet

Insert a module in the VB editor

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

add reference to VB editor to copy code from one sheet to another sheet

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

VBA code showing the code has been moved to destination sheet

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.




Share The Knowledge

Random Posts