Open Macros from another workbook

[Analytic]

Hi,

Here is a code:

[COLOR=#333333]Private Sub CommandButton1_Click()


Dim varNames1 As Variant
Dim varNames2 As Variant


Dim lngArr As Long
Dim wb As Workbook


varNames1 = Array("FIRST\GRAND EVALUATIONS.xlsm")
For lngArr = LBound(varNames1) To UBound(varNames1)
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames1(lngArr))
  Application.Run "'GRAND EVALUATIONS.xlsm'!Macro1"
  wb.Close True
  Set wb = Nothing
Next lngArr




varNames2 = Array("FIRST - Copy\GRAND EVALUATIONS.xlsm")
For lngArr = LBound(varNames2) To UBound(varNames2)
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames2(lngArr))
  Application.Run "'GRAND EVALUATIONS.xlsm'!Macro1"
  wb.Close True
  Set wb = Nothing
Next lngArr


ActiveWorkbook.Save
ActiveWorkbook.Close


End Sub



[/COLOR]

Firstly the above code is supposed to open Macro1 of

[COLOR=#333333]varNames1 = Array("FIRST\GRAND EVALUATIONS.xlsm")
[/COLOR][COLOR=#333333]For lngArr = LBound(varNames1) To UBound(varNames1)[/COLOR]
[COLOR=#333333]  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames1(lngArr))[/COLOR]
[COLOR=#333333]  Application.Run "'GRAND EVALUATIONS.xlsm'!Macro1"[/COLOR]
[COLOR=#333333]  wb.Close True[/COLOR]
[COLOR=#333333]  Set wb = Nothing[/COLOR]
[COLOR=#333333]Next lngArr[/COLOR]

and it does that.

Secondly it was also expected to open Macro1 of

[COLOR=#333333]varNames2 = Array("FIRST - Copy\GRAND EVALUATIONS.xlsm")
[/COLOR][COLOR=#333333]For lngArr = LBound(varNames2) To UBound(varNames2)[/COLOR]
[COLOR=#333333]  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames2(lngArr))[/COLOR]
[COLOR=#333333]  Application.Run "'GRAND EVALUATIONS.xlsm'!Macro1"[/COLOR]
[COLOR=#333333]  wb.Close True[/COLOR]
[COLOR=#333333]  Set wb = Nothing[/COLOR]
[COLOR=#333333]Next lngArr

[/COLOR]

but it doesn't even open the file varNames2 = Array("FIRST - Copy\GRAND EVALUATIONS.xlsm").

Please provide me with some solutions to solve this problem.

Thank you

 

[Analytic]

The purpose is to calculate new values. and yes Results.xlsx does close.

 

[AlphaFrog]

Remove these two lines (red) from Macro1

Sub Macro1()
Dim varNames1 As Variant
Dim lngArr As Long
Dim wb As Workbook

varNames1 = Array("CAL1\RESULTS.xlsx")
For lngArr = LBound(varNames1) To UBound(varNames1)
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames1(lngArr))
  wb.Close True
  Set wb = Nothing
Next lngArr

[COLOR=#ff0000]    ActiveWorkbook.Save
    ActiveWorkbook.Close[/COLOR]
    
End Sub

They are closing the ActiveWorkbook which at that point is GRAND EVALUATIONS.xlsm. Then back in your main workbook ENTRY BOARD.xlsm, the macro will try to close GRAND EVALUATIONS.xlsm again with wb.Close. So you have two different macros that are closing the same workbook.

 

[Analytic]

Remove these two lines (red) from Macro1

Sub Macro1()
Dim varNames1 As Variant
Dim lngArr As Long
Dim wb As Workbook

varNames1 = Array("CAL1\RESULTS.xlsx")
For lngArr = LBound(varNames1) To UBound(varNames1)
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & varNames1(lngArr))
  wb.Close True
  Set wb = Nothing
Next lngArr

[COLOR=#ff0000]    ActiveWorkbook.Save
    ActiveWorkbook.Close[/COLOR]
    
End Sub

They are closing the ActiveWorkbook which at that point is GRAND EVALUATIONS.xlsm. Then back in your main workbook ENTRY BOARD.xlsm, the macro will try to close GRAND EVALUATIONS.xlsm again with wb.Close. So you have two different macros that are closing the same workbook.

Thanks a lot AlphaFrog . That solved the problem. I do appreciate your great help.
Cheers

 

[AlphaFrog]

You're welcome. Thanks for the feedback.