PDA

View Full Version : Solved: run macro from one workbok to affect other workbook



mperrah
09-26-2012, 04:23 PM
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
09-26-2012, 04:24 PM
Here is the finished workbook
and I'll post a pre-edit sample produce too.

mperrah
09-26-2012, 04:27 PM
This is his file. Before Edit
The finished is 9-26-12 mp.xls after edit

mperrah
09-27-2012, 10:21 AM
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.