Copy Workbook event codes into new workbook

lambuhere1 · Nov 6th 2003

Hi

I am using cpearsons code to copy modules from one workbook to another workbook. But I am unsucessful to copy codes associated with workbook_open event or workbook_beforesave event. Can anyone help me out in this.

Following code from
http://www.cpearson.com/excel/vbe.htm
is being used

The following procedure will copy Module1 from Book2 to Book1.

Code

Sub CopyOneModule()


Dim FName As String
With Workbooks("Book2")
    FName = .Path & "\code.txt"
    .VBProject.VBComponents("Module1").Export FName
End With
Workbooks("book1").VBProject.VBComponents.Import FName


End Sub

Display More

I want to copy codes associated with workbook events from Book2 to Book1. Can anyone please help me in this?

Thanks

Ram P

Anonymous · Nov 6th 2003

Try the following that will copy all of the code in the ThisWorkbook module of the workbook containing the code to the active workbook. Modify as needed for your case. The check in the first part of the code may not be needed if you are confident the necessary permissions exist.

Code

Sub CopyCode()
    Dim codeto As Object, codefrom As Object, n As Long
    ' Make sure access to the VBProject is allowed
    Dim VBP As Object    ' as VBProject
    If Val(Application.Version) >= 10 Then
        On Error Resume Next
        Set VBP = ActiveWorkbook.VBProject
        If Err.Number <> 0 Then
            MsgBox "Your security settings do not allow this procedure to run." _
                 & vbCrLf & vbCrLf & "To change your security setting:" _
                 & vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." & vbCrLf _
                 & " 2. Click the 'Trusted Sources' tab" & vbCrLf _
                 & " 3. Place a checkmark next to 'Trust access to Visual Basic Project.'", _
                   vbCritical
            Exit Sub
        End If
        On Error GoTo 0
    End If
    Set codeto = ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").codemodule
    Set codefrom = ThisWorkbook.VBProject.VBComponents("ThisWorkbook").codemodule
    sNewLine = codefrom.Lines(1, codefrom.CountOfLines)
    With codeto
        n = .CountOfLines + 1
        .InsertLines n, sNewLine
    End With
End Sub

Display More

lambuhere1 · Nov 7th 2003

Derk,

Thanks a lot for your reply. It was useful

Ram P

Copy Workbook event codes into new workbook

Participate now!

Share