I have a workbook which opens up another workbook (filename is based on a cell value) and then runs a macro called Single_sector within that file.
It opens the file perfectly fine but doesn't run the macro. Any ideas?
Sub run_all()
Dim Location
On Error Resume Next
'Location of file to open
Location = Worksheets("Main").Range("folder_location").Value
'Open F&V File
Application.Workbooks.Open Location & Range("fv_file").Value
'Run Macro
Run ("Single_sector")
End Sub
Steps to Make a Macro run When a Workbook is Opened In the window that appears, go to the left drop down menu and select Workbook: If the default text for Open doesn't appear, then look to the right drop down menu and select Open from the list: Type whatever macro code you want to run in the middle and that's it!
Steps to run macro automatically when the workbook opens: Step 1: Go to the developer's menu and then go to the visual basic. Step 2: Go to ThisWorkbook Tab. Step 3: Write down Private Sub Workbook_open() and hit enter.
Place the following code in the macro calling the other workbook:
Location = Worksheets("Main").Range("folder_location").Value
Set wb = Workbooks.Open(Location & Range("fv_file").Value)
Application.Run "'" & wb.Name & "'!" & strSubToRun, Parameters
Set wb = Nothing
Parameters is an array of arguments that you want to pass, so the sub in the other workbook should look something like
Public Sub TheSub(ParamArray X())
Dim i As Long
Sheet1.Cells(1, 1).Value = "Parameters passed:"
For i = 0 To UBound(X(0))
Sheet1.Cells(i + 2, 1).Value = CStr(X(i))
Next
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With