Menu
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?
229
Open both the workbook that contains the macro you want to copy, and the workbook where you want to copy it. On the Developer tab, click Visual Basic to open the Visual Basic Editor. , or press CTRL+R . In the Project Explorer pane, drag the module containing the macro you want to copy to the destination workbook.
Copying data with VBA is a common process solved with well written code. Lets say we want to copy data from Sheet1 to Sheet2 with the help of code on a regular basis. The following Excel VBA procedure copies data from the active sheet and pastes it in the first blank cell at the bottom of a range in another worksheet.
If your workbook contains a lot of sheets then you can right-click the tab navigation buttons to see a list of all visible sheets. You can then double-click a sheet in the list to jump to it.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
