Copy Sheet code module from PERSONAL.XLSB to new workbook

Question

I am writing code that is attempting to copy code modules and userforms from PERSONAL.XLSB to a new workbook. The code should copy the following from PERSONAL.XLSB!Sheet1 to the new workbook, Book1.xlsx!Sheet1. The code runs without error, but the code does not copy. Book1.xlsx!Sheet1 is still empty. Any help is greatly appreciated.

Private Sub Workbook_FollowHyperlink(ByVal Target As Hyperlink)

    If Target.TextToDisplay = "Click to Run Hello World" Then
       Run HelloWorld
    End If

End Sub

The macro in PERSONAL.XLSB!Module1 to copy the code:

Sub CodeCopy()

'Macro to copy the macro module from PERSONAL.XLBS!Sheet1 to Test.xls!Sheet1.

Dim i          As Integer
Dim NewSh      As Worksheet
Dim SrcCmod    As VBIDE.CodeModule
Dim DstCmod    As VBIDE.CodeModule

Set SrcCmod = Workbooks("PERSONAL.XLSB").VBProject.VBComponents("Sheet1").CodeModule
Set DstCmod = Workbooks("Book1.xlsx").VBProject.VBComponents("Sheet1").CodeModule

For i = 1 To SrcCmod.CountOfLines
    DstCmod.InsertLines i, SrcCmod.Lines(i, 1)
Next i

End Sub

blahblahblah blahblahblah · Accepted Answer · 2014-02-12T17:10:53

Thought of a work-around -- merely copying PERSONAL.XLSB!Sheet1 to the new workbook. The contained code will copy as well.

Sub CopySheetCode()

    Dim CurrentSheet As Worksheet

    Windows("PERSONAL.XLSB").Activate
    Worksheets("Sheet1").Copy Before:=Workbooks("Book1.xlsx").Sheets(1)

End Sub

Copy Sheet code module from PERSONAL.XLSB to new workbook

1 Answers