I have this code which copies a specific routine into the
Thisworkbook module of a specific workbook.

There is code before this routine which I wish not to
copy, but I have now added code after this routine.

I could use the copy statements for each of the extra
routines, but I wondered if there was another way?

Public Sub CopySheetChangeEventFromAnyBook _
(ByVal NewWorkbookName As String)

'This will only execute if the calling Workbook is unprotected.

Dim SourceCodeModule As VBIDE.CodeModule
Dim DestCodeModule As VBIDE.CodeModule
Dim wb As Workbook, StartCopyLine As Long
Dim NumberOfLines As Long

Set SourceCodeModule = _
ThisWorkbook.VBProject. _
VBComponents("ThisWorkbook").CodeModule

Set DestCodeModule = _
Workbooks(NewWorkbookName & ".xls").VBProject. _
VBComponents("ThisWorkbook").CodeModule

StartCopyLine = SourceCodeModule.ProcStartLine _
("Workbook_SheetChange", vbext_pk_Proc)
NumberOfLines = SourceCodeModule.ProcCountLines _
("Workbook_SheetChange", vbext_pk_Proc)

DestCodeModule.InsertLines DestCodeModule.CountOfLines + 1, _
SourceCodeModule.Lines(StartCopyLine, NumberOfLines)

End Sub

Could I therefore tell it to start with the "Workbook_SheetChange"
routine, and then copy all following routines?

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.786 / Virus Database: 532 - Release Date: 29/10/2004

Try getting the number of lines to copy like this:

NumberOfLines = SourceCodeModule.CountOfLines - StartCopyLine

--
Jim Rech
Excel MVP

Many thanks.

Regards.

"Jim Rech" wrote in message
...
Try getting the number of lines to copy like this:

NumberOfLines = SourceCodeModule.CountOfLines - StartCopyLine

--
Jim Rech
Excel MVP




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.786 / Virus Database: 532 - Release Date: 29/10/2004