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

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 CodeModuleSet src = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModuleSet dest = Workbooks("Book3").VBProject.VBComponents("ThisWorkbook") _    .CodeModuledest.DeleteLines 1, dest.CountOfLinesdest.AddFromString src.Lines(1, src.CountOfLines)

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.

Patrick's code does not work for Worksheets (in fact, it will transfer the code to the wrong module). A workaround is to create a new sheet in the destination workbook, and then copy the code over (optionally you can copy and paste the worksheet data/functions/formatting as well).

The other thing that doesn't work is UserForms. You can copy the code over, but I'm not aware of any way to copy the actual form (including all the controls) without using the export/import method.

Expanding on Patrick's code:

'Needs reference to : Microsoft Visual Basic for Application Extensibility 5.3 ,'or run this code : thisworkbook.VBProject.References.AddFromFile "C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"'from immediate window (ctrl+G) or create a small sub' What works:   Successfully tranfsers Modules with code and name'               Copies userform code and name only, but the form is blank (does not transfer controls)'               Copies code in sheets but no content (optionally add code to copy & paste content)'               Successfully transfers Classes with code and nameOption ExplicitPublic Sub CopyComponentsModules() 'copies sheets/Thisworkbook/Userforms/Modules/Classes to a new workbook    Dim src As CodeModule, dest As CodeModule    Dim i&    Dim WB_Dest As Workbook    Dim Ref As Reference    Dim Comp As VBComponent    Dim sht As Worksheet    Debug.Print "Starting"    Set WB_Dest = Application.Workbooks.Add    On Error Resume Next 'needed for testing if component already exists in destination WorkBook and for cross-references        For Each Comp In ThisWorkbook.VBProject.VBComponents            Debug.Print Comp.Name & " - "; Comp.Type            Err.Clear            'Set Source code module            Set src = Comp.CodeModule  'ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule            'Test if destination component exists first            i = 0            i = Len(WB_Dest.VBProject.VBComponents(Comp.Name).Name)            If i <> 0 Then 'or: if err=0 then                Set dest = WB_Dest.VBProject.VBComponents(Comp.Name).CodeModule            Else 'create component                Err.Clear                If Comp.Type = 100 Then                    Set sht = WB_Dest.Sheets.Add                    Set dest = WB_Dest.VBProject.VBComponents(sht.Name).CodeModule                    WB_Dest.VBProject.VBComponents(sht.Name).Name = Comp.Name                    sht.Name = Comp.Name                Else                    With WB_Dest.VBProject.VBComponents.Add(Comp.Type)                        If Err.Number <> 0 Then                            MsgBox "Error: Component " & Comp.Name & vbCrLf & Err.Description                        Else                            .Name = Comp.Name                            Set dest = .CodeModule                        End If                    End With                End If            End If            If Err.Number = 0 Then                'copy module/Form/Sheet/Class 's code:                dest.DeleteLines 1, dest.CountOfLines                dest.AddFromString src.Lines(1, src.CountOfLines)            End If        Next Comp        'Add references as well :        For Each Ref In ThisWorkbook.VBProject.References            WB_Dest.VBProject.References.AddFromFile Ref.FullPath        Next Ref    Err.Clear: On Error GoTo 0    Set Ref = Nothing    Set src = Nothing    Set dest = Nothing    Set Comp = Nothing    Set WB_Dest = NothingEnd Sub