Import "ThisWorkbook" macro to another Workbook

user_6156353 0
Viewed 1672

I need VBA code to import a procedure into ThisWorkbook scope within another workbook.

I have code to import a module into another workbook, but how to import this procedure into ThisWorkbook scope instead of into a module scope?

Sub TransferModule()
    Const modul As String = "Misc"
    Const tempfile As String = "/Users/Roman/Desktop/temp.bas"

    Dim WBK As Workbook

    On Error Resume Next

    Set WBK = Workbooks.Add

    ThisWorkbook.VBProject.VBComponents(modul).Export tempfile

    WBK.VBProject.VBComponents.Import tempfile

    Kill tempfile
End Sub

This takes module Misc from "This" workbook, exports it to temp.bas and then import it to another workbook. However I need it to import not into a module, but into ThisWorkbook scope. It's a Private Sub Workbook_BeforeClose event.

vbe import vba excel
1 Answers

Rather than creating the new workbook with:

Set WBK = Workbooks.Add

consider using something like:

Sub lkjhgf()
    ThisWorkbook.SaveCopyAs ("C:\Users\Garys\Desktop\new.xlsm")
End Sub

The newly created workbook will have all the ThisWorkbook code that the original has.

(Of course, you may have more clean-up to do to remove stuff you don't need copied.)

EDIT#1:

See Vogelaar's answer here:

Loading a subroutine into "ThisWorkbook" using VBA

user_24746560
Related