I've written some macros at work that other people would like to use. Rather
than using an add-in, I was wondering if it would be better to write a macro
to copy the functions to the other users' "Personal.xls" workbook using Chip
Pearson's method.

I tried the code below, but I can't get it to work. Does this mean that
copying modules to "Personal.xls" is prohibited? In which case, it would be
better to use an Add-in. If I add more macros to the Add-in in the future,
will they automatically be loaded to other users' workbooks (once the add-in
is installed)?

----------------------------------------------------------------------------------------
Sub CopyModule()

On Error GoTo Errorhandler
Dim FName As String, fldr As String

' Obtain username
uname = InputBox("Enter Username", "Username")
fldr = "C:\Documents and Settings\" & uname & "\Application
Data\Microsoft\Excel\XLSTART"
personal = fldr & "\Personal.xls"

' Test to see if "Personal.xls" exists
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(personal) Then
Workbooks.Add
ActiveWorkbook.SaveAs (personal)
End If

' Copy this macro to "Personal.xls"
With ThisWorkbook
FName = .Path & "\code.txt"
.VBProject.VBComponents("sub_sumColor").Export FName
End With
Workbooks(personal).VBProject.VBComponents.Import FName
Kill FName

Exit Sub

Errorhandler:
msg = "There was a problem copying the macro. Please verify that " & _
"you have followed all the instructions. If you still need help, see the
Intern."
Title = "Fatal Error"
Style = vbOKOnly + vbCritical
response = MsgBox(msg, Style, Title)

End Sub
-----------------------------------------------------------------------------------

Thanks,
Pflugs