Results 1 to 4 of 4

Thread: Copy macros

  1. #1
    Join Date
    Dec 2004
    Posts
    78

    Question Unanswered: Copy macros

    Hi,

    I have a template that holds some standard macros required in all worksheets that our company uses. Is there a way to replicate/copy these macros automatically to a new file when a new file is created.

    Thanks.

    ~BS

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    what you could do is export your module to a standard location, then just import on creation of a new workbook,

    that would probably work well enough, if you want to do this programatically then you would have to program the VBE,
    post back if you would like a hand with any of the above
    Dave

  3. #3
    Join Date
    Dec 2004
    Posts
    78
    Is there a way to export the whole project as I have multiple modules ans sheets.

    Thanks

    ~BS

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ok then id suggest putting a folder template together and adding this module to your workbook and running this will pick up all modules from a folder

    Code:
    Sub addModules()
    'uses microsoft visual basic for applications extensibility 5.3
    Dim i As Integer
    Dim mypath As String
    
        'get location of files
        mypath = Application.GetOpenFilename
        mypath = Mid$(mypath, 1, InStrRev(mypath, "\"))
        
        'set up an array of your code modules
        With Application.FileSearch
            .NewSearch
            .FileType = msoFileTypeAllFiles
            .LookIn = Application.GetOpenFilename
            .SearchSubFolders = True '(or false)
            If .Execute > 0 Then
                For i = 1 To .FoundFiles.Count
                    'import all modules found
                    Application.VBE.ActiveVBProject.VBComponents.Import (.FoundFiles(i))
                Next i
            End If
        End With
        
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •