Forum Discussion

aekbus's avatar
aekbus
Brass Contributor
Jun 13, 2026

Macros in Excel Programs

I am developing a macro enabled spreadsheet that creates a second macro enabled spreadsheet upon exit.  Is there a way for the macro in my main spreadsheet to create UserForms and Modules in the second spreadsheet?  My goal here is to distribute the second spreadsheet to users and their use of it will be controlled by the UserForms and macros within it.

excel
Macros and VBA

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jun 14, 2026

    I think you can do this. The most practical way is to keep a master template workbook (.xlsm) that already contains all the UserForms and macros you want users to have. Then, from your main spreadsheet, just copy that template to create a new distributed file.

    This avoids security issues with modifying VBA projects and is much more reliable.

    Here's a simple VBA example:

    Sub DeployUserWorkbook()
    Dim templatePath As String
    Dim newFilePath As String
    
    ' Path to your template (already contains UserForms & modules)
    templatePath = "C:\MyTemplates\UserTemplate.xlsm"
    
    ' Where to save the new workbook (includes timestamp)
    newFilePath = "C:\DeployedFiles\UserWorkbook_" & Format(Now, "yyyymmdd_hhmmss") & ".xlsm"
    
    ' Copy the template to a new file
    FileCopy templatePath, newFilePath
    
    ' Optional: open the new workbook for any additional data
    Dim wb As Workbook
    Set wb = Workbooks.Open(newFilePath)
    
    ' Add any user-specific data here if needed
    ' wb.Sheets("Settings").Range("A1").Value = "User info"
    
    wb.Close SaveChanges:=True
    
    MsgBox "Workbook created at: " & newFilePath
End Sub

    If you really need to dynamically import UserForms/modules (export/import method), I can maybe prepare that code too—just let me know. But for most cases, the template copy above is faster, cleaner, and won't break due to security settings.

    My answers are voluntary and without guarantee!

    Hope this will help you.