Excel VBA to open a workbook and switch to a different worksheet

Question

Good Afternoon. I am currently very novice a VBA and am trying to create a simple code that will open a workbook, flip to a specific worksheet (Transaction Analysis) and run two macros. Then is should flip to worksheet "Fund Trend" and run those same two macros, then save and close. I found this code online and have slightly modified to try to open a specific sheet within the workbook with no luck.

If I exclude the line regarding "ActiveWorkbook.Sheets("Transaction Analysis") then the script works correctly for the open worksheet. It is when I try to add anything referencing a specific worksheet within the workbook that I run into issues. The the script shown below, the workbook opens and that's it. The script stops there and does not run the macros.

I would really appreciate any help or suggestions! The idea is to schedule schedule the execution of the final vbscript with task scheduler. Thank You!

Dim args, objExcel

Set args = wscript.Arguments
set objExcel = CreateObject("Excel.Application")

objExcel.workbooks.open args(0)
objExcel.Visible = True

ActiveWorkbook.Sheets("Transaction Analysis")

objExcel.Run "Clear"

objExcel.Run "SecurityDistribution"

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close(0)
objExcel.Quit

No Luck doesn't tell us what is happening...be more specific about what you expect and what is occurring. Also as an FYI, You need to enable access to the VBA Project model in Options>>Trust Center Settings>>Macros — Sorceri
Thank You. My comment has been updated with more information. — Brad H_KC
I've also tried ActiveWorkbook.Sheets("Transaction Analysis").Activate and at this point, the workbook opens, but nothing happens after that. — Brad H_KC

ASH ASH · Accepted Answer · 2016-05-02T19:27:42

Are you saying to want to run a Macro in another Workbook other than the one that has focus? If that's all it is, try this one-liner.

Application.Run "MacroBook!MacroName"

Notice, you don't need to select a specific Worksheet to run a Macro. You do, however, need to select the right Workbook!

Excel VBA to open a workbook and switch to a different worksheet

1 Answers