3

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
romanzdk
  • 930
  • 11
  • 30

1 Answers1

4

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

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Thank you, however I have macro that copy SheetA from Primary Workbook, to newWorkbook and in this newWorkbook I need to add Private Sub to "ThisWorkbook" if its possible. This PrivateSub I dont really need to have in Primary Workbook. – romanzdk Oct 05 '17 at 12:14