CODEWITHSUNDEEP
vbaexcel
Yikun
Yikun

Reputation: 29

Error when use Application.run to run a macro from another workbook

I need to run multiple scenarios in a excel model. In the current model, each scenario has to be load manually, and each run takes over 3 hours. I also need to save the model result in a new workbook.

I'm writing a macro to allow scenarios automatically load in the model, run, and save as a new workbook. My code is below. The problem is when it goes to the code line "Application.run ... ", Run time error 1004: Application defined or object defined error.

Please help! 

Private Sub CommandButton1_Click()
Dim ScentoRun, Path, N As String
Dim DestCom, Target As Range
Dim SCount, x As Integer

Path = "F:\"

SCount = Workbooks("Scenarios to Run").Worksheets("Sheet1").Cells(6, Columns.Count).End(xlToLeft).Column

For x = 1 To SCount
Workbooks.Open Filename:=Path & "The Model.xlsm"
Workbooks("Scenarios to Run").Worksheets("Sheet1").Columns(x).Copy
Workbooks("The Model").Worksheets("Scenarios").Columns(6).PasteSpecial
ScentoRun = Workbooks("The Model").Worksheets("Scenarios").Range("F6").Value

Application.DisplayAlerts = False
Workbooks("The Model").SaveAs Filename:=Path & ScentoRun, FileFormat:=52
Application.DisplayAlerts = True

Workbooks(ScentoRun).Worksheets("Results").Range("F8") = Workbooks(ScentoRun).Worksheets("Scenarios").Range("F6")

Workbooks(ScentoRun).Activate
N = Workbooks(ScentoRun).name
Application.Run "'N'!loadScenario"  '----- ERROR HERE
Workbooks(ScentoRun).Save
Workbooks(ScentoRun).Close (True)

Next x

End Sub

Upvotes: 0

Views: 1254

Answers (1)

Tim Williams
Tim Williams

Reputation: 166156

Application.Run "'" & N & "'!loadScenario"  '----- ERROR HERE

Upvotes: 3

Related Questions