I've been using the lines below to compy VBA modules from one workbook to another and I don't know if there is an easier way, but they have been working fine:
Set srcVba = srcWbk.VBProject
Set srcModule = srcVba.VBComponents(moduleName)
srcModule.Export (path) 'Export from source
trgtVba.VBComponents.Remove VBComponent:=trgtVba.VBComponents.Item(moduleName) 'Remove from target
trgtVba.VBComponents.Import (path) 'Import to target
However now I need to copy VBA code that is in a Sheet, not in a Module. The above method doesn't work for that scenario.
What code can I use to copy VBA code in a sheet from 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 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)