Sheet module VBA not copied

[moonbeam]

Please help if you are able.

I have found that if I manually copy a sheet by right
click on the sheet tab and select "move or copy" then send
the sheet to another workbook it takes the VBA code stored
in the sheet module with it.

But, if I run the VBA to do this, the VBA code stored in
the sheet module is not included in the copy. I need to be
able to do this from code. Can someone supply me with the
VBA to do this (Excel XP/Win 2000)?

Your example code will be most appreciated. Thanks in
advance for your assistance.

 

[Ron de Bruin]

ActiveSheet.Copy

will copy the sheet with the code in a new workbook

Or this between workbooks
book2.xls must be open

Sub test()
Dim mybook As Workbook
Dim basebook As Workbook
Set basebook = ThisWorkbook
Set mybook = Workbooks("book2.xls")
basebook.Worksheets(1).Copy after:= _
mybook.Sheets(mybook.Sheets.Count)
End Sub