incorporating a macro import within another sub

[Reddog94]

The following code (blue font) finds specific worksheets in an active workbook, and copies them into a new workbook then names the new workbook the same name of the worksheet. It all works perfectly.

The second code below (red font) imports some vb into the new workbook. It works fine if I manually open the new workbook and run it in the immediate window. However, I would like to incorporate the red code into the blue code so it all runs as one. I've tried inserting the red line after the "ws.copy after" line and after the ".saveas" line, but no dice.

I think it is because I'm not making the new book active (but that is an uneducated guess). Does anyone know what I'm doing wrong?


For Each ws In wb.Worksheets
If UCase(Left(ws.Name, 2)) = "CC" Then
Set NewBook = Workbooks.Add
With NewBook
.Title = ws.Name
ws.Copy After:=NewBook.Worksheets("Sheet3")

For Each ws2 In NewBook.Worksheets
If ws2.Name <> ws.Name Then
ws2.Delete
End If
Next
.SaveAs Filename:="C:\Users\lmcginle\Desktop\" & ws.Name
.Close SaveChanges:=True
End With
End If
Next

wb.Activate

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Application.VBE.ActiveVBProject.VBComponents.Import ("T:\Finance Mgmt\Reporting\MacroExport.bas")

 

[Reddog94]

Yep, this is a copy paste from my file:

On Error GoTo ErrorHandler

Dim VBProj As Object
Dim VBImp As String

Set VBProj = ActiveWorkbook.VBProject
VBImp = ("T:\Finance Mgmt\Reporting\MacroExport.bas")

VBProj.VBComponents.Import VBImp

ErrorHandler:
MsgBox Error.Description

Is it because I have Option Explicit at the top? (That may be a dumb question...sorry, new to vba.)

 

[AlphaFrog]

Can you comment out the On Error line and determine which line of code it errors on?

Edit:
Try this...

On Error GoTo ErrorHandler

Dim VBProj As Object
Dim VBImp As String

Set VBProj = ActiveWorkbook.VBProject
VBImp = ("T:\Finance Mgmt\Reporting\MacroExport.bas")

VBProj.VBComponents.Import VBImp
[COLOR="Red"]Exit Sub[/COLOR]
ErrorHandler:
MsgBox Error

 

[Reddog94]

No message box pops up, any way I try it. If it helps, the entire code for the file is below.

Option Explicit
Sub Export()
'Saves the export file in Finance Mgmt\Reporting
Dim VBComp As Object
Dim VBProj As Object

On Error Resume Next
Kill ("T:\Finance Mgmt\Reporting\MacroExport.bas")
On Error Resume Next
Set VBProj = ThisWorkbook.VBProject
Set VBComp = VBProj.VBComponents("module4")

VBComp.Export ("T:\Finance Mgmt\Reporting\MacroExport.bas")

End Sub
Sub Import()

On Error GoTo ErrorHandler
Dim VBProj As Object
Dim VBImp As String

Set VBProj = ActiveWorkbook.VBProject
VBImp = ("T:\Finance Mgmt\Reporting\MacroExport.bas")

VBProj.VBComponents.Import VBImp

End Sub
ErrorHandler:
MsgBox Error.Description


Sub Copy_Save_CC()
On Error GoTo ErrorHandler

Dim wb As Workbook
Dim NewBook As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim VBProj As Object
Dim VBImp As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set wb = ActiveWorkbook

'Finds each worksheet that begins with "CC" and copies each into a new book, then deletes any extra sheets and saves wkb with same name as wks.
For Each ws In wb.Worksheets
If UCase(Left(ws.Name, 2)) = "CC" Then
Set NewBook = Workbooks.Add
With NewBook
.Title = ws.Name
ws.Copy After:=NewBook.Worksheets("Sheet3")

For Each ws2 In NewBook.Worksheets
If ws2.Name <> ws.Name Then
ws2.Delete
End If
Next

Set VBProj = ActiveWorkbook.VBProject
VBImp = ("T:\Finance Mgmt\Reporting\MacroExport.bas")

VBProj.VBComponents.Import VBImp

On Error Resume Next

.SaveAs Filename:="C:\Users\Reddog94\Desktop\" & ws.Name
.Close SaveChanges:=True
End With
End If
Next

wb.Activate

Application.DisplayAlerts = True
Application.ScreenUpdating = True
ErrorHandler:
MsgBox Error.Description

End Sub
Sub CreateGROUPSummary()
'Runs BPC Expansion
Application.Run ("MNU_eTOOLS_EXPAND")
'Updates employee data formulas to match new expansion
Worksheets("Template").Range("$B$165").Copy _
Worksheets("Template").Range("$B$166:$B$375")

'Generates CC Summaries based list in DRIVER sheet
Dim sCount As String
Dim vCC As Variant
vCC = [GROUP_MEMBERS]
Dim iMembers As Long
iMembers = UBound(vCC)
For iMembers = 1 To iMembers
If vCC(iMembers, 1) <> "0" Then
Sheets("Template").Select
Sheets("Template").Copy Before:=Sheets("DRIVERS")
ActiveSheet.Name = vCC(iMembers, 1)
ActiveSheet.Range("$A$6:$A$399").AutoFilter Field:=1, Criteria1:="ALWAYS"
ActiveSheet.Range("138:153").EntireRow.Hidden = True
ActiveSheet.Range("E3:H3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
On Error Resume Next
Next
'Launches show/hide sub export.
Call Export
'Launches new wkb creations sub.
Call Copy_Save_CC
'Hides detail and re-protects sheet
ThisWorkbook.Sheets("Summary").Range("$A$6:$A$399").AutoFilter Field:=1, Criteria1:="ALWAYS"
ThisWorkbook.Sheets("Summary").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
'Hides Drivers and Template sheets
ThisWorkbook.Sheets("DRIVERS").Visible = False
ThisWorkbook.Sheets("Template").Visible = False
'Protects Workbook
ActiveWorkbook.Protect Password:="SECRET", Structure:=True, Windows:=False
End Sub

 

[AlphaFrog]

Error.Description was causing the error. Try this...

Sub Import()

    On Error GoTo ErrorHandler
    Dim VBProj As Object
    Dim VBImp  As String

    Set VBProj = ActiveWorkbook.VBProject
    VBImp = ("T:\Finance Mgmt\Reporting\MacroExport.bas")

    VBProj.VBComponents.Import VBImp
Exit Sub

ErrorHandler:
MsgBox [COLOR="Red"]Err.Description[/COLOR]

End Sub

 

[Reddog94]

The import is working! Thank you thank you thank you. But...the import is coming into the original template file, and I want it to import into the newly created workbook. Here are the codes - the first (red) is the one we've been working on. It is called in the second code (blue).

I thought that I had this right, since the import call is within a With statement for the newbook, but guess not.

Sub Import()

On Error GoTo ErrorHandler
Dim VBProj As Object
Dim VBImp As String
Set VBProj = ActiveWorkbook.VBProject
VBImp = ("T:\Finance Mgmt\Reporting\MacroExport.bas")

VBProj.VBComponents.Import VBImp

Exit Sub
ErrorHandler:
MsgBox (Err.Description)
End Sub

Sub Copy_Save_CC()
On Error GoTo ErrorHandler

Dim wb As Workbook
Dim NewBook As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim VBProj As Object
Dim VBImp As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set wb = ActiveWorkbook

'Finds each worksheet that begins with "CC" and copies each into a new book, then deletes any extra sheets and saves wkb with same name as wks.
For Each ws In wb.Worksheets
If UCase(Left(ws.Name, 2)) = "CC" Then
Set NewBook = Workbooks.Add
With NewBook
.Title = ws.Name
ws.Copy After:=NewBook.Worksheets("Sheet3")

Call Import

For Each ws2 In NewBook.Worksheets
If ws2.Name <> ws.Name Then
ws2.Delete
End If
Next

On Error Resume Next

.SaveAs Filename:="C:\Users\lmcginle\Desktop\" & ws.Name
.Close SaveChanges:=True
End With
End If
Next

wb.Activate

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox (Err.Description)

End Sub

 

[AlphaFrog]

Forum Tip: Pasting VBA code in the forum editor
It would be best if you surround your VBA code with code tags e.g [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier.
When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign #.

I would suggest not using the macro Import as it is basically does just one thing. The VBComponents.Import line is incorporated within the Copy_Save_CC macro below along with some other changes.

Try something like this...

[color=darkblue]Sub[/color] Copy_Save_CC()
    
    [color=darkblue]Dim[/color] ws [color=darkblue]As[/color] Worksheet
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] ErrorHandler
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    [color=green]' Finds each worksheet that begins with "CC" and copies\saves each[/color]
    [color=green]' to a new workbook with the same name as the CC worksheet[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] ws [color=darkblue]In[/color] ActiveWorkbook.Worksheets
        [color=darkblue]If[/color] UCase(ws.Name) [color=darkblue]Like[/color] "CC*" [color=darkblue]Then[/color]
            ws.Copy [color=green]'Copies the worksheet to a new workbook[/color]
            [color=darkblue]With[/color] ActiveWorkbook
                .VBProject.VBComponents.Import "T:\Finance Mgmt\Reporting\MacroExport.bas"
                .Close SaveChanges:=True, Filename:="C:\Users\lmcginle\Desktop\" & ws.Name
            [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] ws
    Application.ScreenUpdating = [color=darkblue]True[/color]
    [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
ErrorHandler:
    MsgBox Err.Description

[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Reddog94]

Yep, no luck. I'll keep trying. Thanks very much for all your help.

 

[Reddog94]

Who's the dummy that was trying to import a macro into a non-macro enabled workbook?

...that'd be me

Problem solved. Thanks again.