Copy VBA code from a Sheet in one workbook to another?

If anyone else lands here searching for VSTO equivalent of Chel's answer, here it is:

void CopyMacros(Workbook src, Workbook dest)
{
  var srcModule = src.VBProject.VBComponents.Item(1).CodeModule;
  var destModule = dest.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);

  destModule.CodeModule.AddFromString(srcModule.Lines[1, srcModule.CountOfLines]);
}

Things to note:

1. You must add reference to Microsoft.Vbe.Interop to do this stuff.
2. I'm adding a new general module to the destination workbook, so I didn't need to call DeleteLines. YMMV.

You can't remove and re-import the VBComponent, since that would logically delete the whole worksheet. Instead you have to use CodeModule to manipulate the text within the component:

Dim src As CodeModule, dest As CodeModule

Set src = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
Set dest = Workbooks("Book3").VBProject.VBComponents("ThisWorkbook") _
    .CodeModule

dest.DeleteLines 1, dest.CountOfLines
dest.AddFromString src.Lines(1, src.CountOfLines)