copy module to another module 
Author Message
 copy module to another module

Does anyone know how to copy a module (Module 2) to the coding behind a
worksheet (Sheet3 (Central Office))?

Tue, 03 Feb 2004 22:56:57 GMT  
 copy module to another module

You could manipulate the workbook's VBA project with code.  To do this you
need to reference the VBA Extensibility library (in the VBA Editor for your
workbook, go to Tools > References and check "Visual Basic Extensibility

Then you could use code like this to append the code from one module to

    Dim oBook As Workbook
    Dim oModule1 As VBComponent, oModule2 As VBComponent

    Set oBook = Workbooks.Open("c:\book1.xls")
    Set oModule1 = oBook.VBProject.VBComponents("Module1")
    Set oModule2 = oBook.VBProject.VBComponents("Sheet1")

    'Get the code in Module1
    Dim n As Long, sCode As String
    n = oModule1.CodeModule.CountOfLines
    sCode = oModule1.CodeModule.Lines(1, n)

    'Append the code from Module1 to the code module for Sheet1
    n = oModule2.CodeModule.CountOfLines
    'oModule2.CodeModule.DeleteLines 1, n 'Use if you want to clear the
code in Sheet1 first
    oModule2.CodeModule.InsertLines n + 1, sCode

Hope this helps,

Lori Turner
Microsoft Developer Support



>>Subject: copy module to another module
>>Date: Fri, 17 Aug 2001 09:56:57 -0500
>>Lines: 6
>>Organization: UW Colleges
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
>>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400


>>Does anyone know how to copy a module (Module 2) to the coding behind a
>>worksheet (Sheet3 (Central Office))?

Mon, 09 Feb 2004 11:58:55 GMT  
 [ 2 post ] 

 Relevant Pages 

1. Modules, Modules, Modules

2. VBA Module to Module

3. Circular Reference Between Modules when 2 Class Modules Listen to Each others Events

4. Referece a module and have it not run the module

5. Edit / change module witin module

6. Class modules 97 - Refering to array within a custom class module

7. Debug a module that changes code in a report module

8. Passing input box value from Form Module to report module

9. Call module functions from outside of module?

10. UserForm code module not visible from normal module

11. Class Module and Module

12. Class Modules & Modules


Powered by phpBB® Forum Software