Tom has attended:
Excel VBA Intro Intermediate course
Access Intermediate course
Running a Macro from another spreadsheet
Hi,
How do you open a spreadsheet and run a macro in it from a macro in another spreadsheet without effecting this other spreadsheet?
Thanks,
Tom
RE: Running a Macro from another spreadsheet
Hi Tom
Thanks for your question.
You could open the worksheet from your macro in another worksheet very easily with something like the following
Workbooks(NAME).sheets(Name).activateYou could then run your second macro by calling it in the activate event of that worksheet.
Hope this helps
Regards
Stephen
RE: Running a Macro from another spreadsheet
Hi Stephen,
Thanks very much for your help. Would you mind explaining a little further. I have tried the following...
Sub RunMacro()
Workbooks("Dist Plan 103a").Sheets("Reforecast").Activate
Application.Run (Workbooks("Dist Plan 103a")!reforecast_figures)
End Sub
Where reforecast_figures is the macro and it doesnt like it, it says Subscript Out of Range.
Sorry about this,
tom
RE: Running a Macro from another spreadsheet
Hi Tom
Sorry for the delay in getting back, I have been on a week's leave.
Your first line of code is fine. However your reforcast_figures macro needs to be called from the open event of your "Dist Plan 103a" workbook. Open this workbook and go to the VBE. In the project explorer double click on the workbook object. Above the code window you will have 2 combo boxes. The left most one will probably say "General". Click on the combo and select Workbook. This should then automatically create an on open sub procerdure. In this procedure type
[code] Call reforcast_figures[\code]
This will then run the macro when the workbook opens
Regards
Stephen
