Copying sheet from 1 workbook to another, disabling macros in the process

[wwbwb]

Greetings all,

I am working on some production reporting workbooks. The users open their PRs and fill in their information. At the end of the day, a tech will open a Master PR which will import all the data for consolidation. Everything is working great.

The PR workbooks have code that runs at open. Basically security stuff to keep them from using toolbars and menus and such. When I use the Master PR to import the data, just copying a range from the PR to the Master PR, all the code runs for each PR that is opened. Here is my question.

Is it possible to prevent the PRs open code from running each time I open it from another workbook?

or will I have to add something to the PR code like: if master PR.xls is open exit sub

 

[par60056]

I was waiting to see if one of the MVPs would reply but since they haven't I'll take a stab and cause the thread to bump.

Does the Master PR book have to be in the same place as the User PR books?

Could you do something with the "Trusted Path" to make the User PR books live in a place that is not a trusted path to the macro copying to the master and this way the open macro wouldn't run?

I looked a bit but did not find a way to set an option on the Workbooks.open or on the application to block running the open macro.

 

[wwbwb]

They don't have to be in the same place, but ideally they should be to keep things simple.

I'm working on my second option of adding code to the beginning of the PR workbook_open code.

So far it seems to be ok. Basically it checks to see if the master pr workbook is open and if it is, it bypasses the security activating code lines and exits the open sub, otherwise if the master pr is not open, it proceeds with the security code.

 

[par60056]

That seems the most sensible solution. I wondered if the problem was that there were multiple "User PR" files in the wild that you would have to track down and update because people had made their own copies.

Is there a reason that you don't have the "User PR" write just the data/sheet to a separate workbook when the user saves it? Then there would be no macro in the file.

 

[wwbwb]

The User PR contains a couple sheets. One where the user inputs their data. The second sheet is a history of their daily work. This way they can go back and see what they did on any given day. Also, this history sheet ties in with one of the managers spreadsheets that pulls monthly totals, by week, for evals.

All the User PRs are kept in one folder and if they try to use their PR outside of their folder, it won't work properly. I create the User PRs from a Template PR where I can make changes and within it, I have an update macro that applies any changes I make to it, to the User PRs. Cause I found out updating 12+ PRs with new code or updates was way too tedious. Smarter not harder, right?