CODEWITHSUNDEEP
vbaexcelimportvbe
romanzdk
romanzdk

Reputation: 1332

Import "ThisWorkbook" macro to another Workbook

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.

Upvotes: 3

Views: 2128

Answers (1)

Gary's Student
Gary's Student

Reputation: 96771

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

Upvotes: 4

Related Questions