Solved: run macro from one workbok to affect other workbook

[mperrah]

I have a co-worker that outputs a report daily that needs to modified. I have a master workbook that I automated the formatting and formula inserting task I perform daily. I want to make a menu item he can click to run the macro on his workbook.
I have the menu part mostly worked out.

I have 2 dilemma's (delemmie?)

1. not sure how to tell my workbook macro to run on other workbook.

2. I can try to copy his worksheet to an import sheet in my workbook and run the macro then output the page with a new name (todays date is how they are named)

Problem just copying is his worksheet is an outline with 3 levels, (autofilter?) when i copy I loose the levels and my macro debugs.

I'll attach what I have. My macro works if I have both workbooks open and hit "alt+F8" and chose the top macro from the list.
It errors when He tries it so I'm trying to make it easier...

I appreciate any suggestions or full blown code too
Thanks
Mark

[mperrah]

Here is the finished workbook
and I'll post a pre-edit sample produce too.

[mperrah]

This is his file. Before Edit
The finished is 9-26-12 mp.xls after edit

[mperrah]

I kept searching knowledge base
and found an open workbook dialog
and activate workbooks(2) by index works now.
I post my result here for posterity

So my macro prompts user to close all workbooks,
then has them choose a file top open
then activates that workbook and runs the macro.

Yeah.