Expanding code from working on one named sheet to multiple named sheets

[BryanD]

I have a bit of code that I've pieced together from searching this forum and from other resources. It works awesome copying rows of data from a source sheet to a single named sheet. What I want to do is build on this code so I can use a single macro to copy multiple rows of data (determined by a value in the source sheet) to specific named sheets. First, some background.

The workbook has a single source sheet called "AllProjects" that contains all the capital projects our organization is currently working on or has planned for the next five years. All kinds of project data exists on this sheet such as project names, current expenditures, forecasted fiscal year expenditures, the project manager name and the project managers's manager (and a bunch more data for each specific project). From this "AllProjects" source sheet I run a macro to look at the Manager's Name in a specific column and copy rows of data to the Manager's sheet. This lets me run a specific report by Manager showing the projects they're responsible for and any pertinent information I want to display for each project. The code basically looks for the manager name "J Smith" and copies the relevant rows of data to a sheet called "J Smith". This particular macro runs great.

Based on reporting needs, I was asked to do a similar report but break it down by Project Manager instead of Manager and this is where I run into problems. I tried modifying the Macro I use for copying project info to the Manager sheets but it's never worked right. I suspect that the problem is being caused by a couple of issues. First I have Managers who are also Project Managers. Even though the Manager and Project Manager names are in two different columns they would contain the same name (e.g., PM name ="J Smith" and Manager name="J Smith". Second because of the way I had to name Project Manager sheets due to the Manager having their own projects. I can't have two sheets named exactly the same so if a Manager has projects they get a Manager sheet called "J Smith" and a Project Manager sheet called "Jsmith". I think the problem is that the code is looking to place the copied rows of data based on the value it's looking up in the source sheet (e.g., "J Smith).

I hope that isn't too confusing.

Yesterday I pieced together some code to work specifically for the transfer of rows of data to the Project Manager sheets. The code works awesome on one one sheet at a time but I'd love to be able to run the macro and autofill all the Project Manager sheets at one time vice having to run 25 different macros to copy information for 25 project managers.

For reference here are the two snippets of code I use.

The code I use to transfer information from the source sheet to the Manager sheets is (maybe this one can be modified to do what I want but at the Project Manager level?):

Please Login or Register  to view this content.

I suspect that the problem of re-purposing the code above for the Project Managers is:

Please Login or Register  to view this content.

If I'm not mistaken this tells the code to copy the relevant rows of data to the sheets named the same as those listed in the array ("1 Manager", etc.).

The code I use to transfer information from the source sheet to an individual Project Manager sheet is:

Please Login or Register  to view this content.

Any help would be appreciated.

[BryanD]

Any suggestions?

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.