"Geoff" wrote in message
...
: Hi
: I am trying to adapt Chip Pearson's code and insert the same procedure
into
: 2 added worksheets.
:
: Strange thing is my code only fails each time I start a new instance of
the
: workbook. Even then, when the code breaks on error with 'Subscript out of
: Range' and I click to continue in Debug mode the code completes without
error
: AND the new module is installed correctly in both new worksheets.
:
<snip

I think you left some info out but what I am understanding is you are
importing a .bas file into excel, then trying to do something with it and
getting a subscript out of range error. This typically happens because your
code is moving faster than the import is so your code is trying to do
something with a module not there yet. Once you break, or after the code is
inserted, everything then works. If I am understanding you correctly, you
need to us ontime while inserting to slow down your macro

Application.OnTime Now, procedu="your_module_name.ImportIt"

Private Sub ImportIt()
Dim vbcomp As VBComponents
Set vbcomp = ThisWorkbook.VBProject.VBComponents
vbcomp.import ("your_macro.bas")
End Sub

Paul D