98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Running a Macro from another spreadsheet
Running a Macro from another spreadsheet
Resolved · Medium Priority · Version 2007
Tue 8 Jun 2010, 15:35Delegate Tom said...
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
Wed 9 Jun 2010, 17:17Trainer Stephen said...
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
Thu 10 Jun 2010, 17:45Delegate Tom said...
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
Mon 14 Jun 2010, 11:39Trainer Stephen said...
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
Mon 21 Jun 2010: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Selecting constant values onlyIf periodically you need to change all your values back to zero, but leave formulas, text and blank cells as they are select the entire worksheet, choose F5 function key, Special and then Constants and choose the appropriate sub-selections. To enter zero in all the selected cells type 0 and then press Ctrl+Enter. |
