Copying Specific Data To Another Sheet

Hi,

Before I explain my problem, I should let you all know that I posted this same question in this same forum, and this is the link to that forum, but because it hasn't had an answer yet and I found more information for your assistance I started this new thread

http://www.excelforum.com/showthread.php?t=642554

Also I posted in this other forum, where I got some responses to my issue and appreciate them, however I tried many times modifying what they suggested and still doesn't work in my situation.

http://www.ozgrid.com/forum/showthre...691#post431691


I will describe my problem now as clear as possible. I'm making a Task list with assignments for each of my team members. Columns A & B describes the ID number of the task, Column C describes the Task itself, Columns D to J are hidden, Column K describes the author of the task.


Column L is the one that have the name (or names) of the person who is in charge of doing that task. What I need to do is to create a macro that searches the name of that person in Column L and once it finds it it will create another sheet with the name of that person. And add to that sheet the entire row of his task.

For example:
|-----------------------------------------------------------------|
|-A-|-B-|--------C---------|---K---|----L------|
|-----------------------------------------------------------------|
|---1---|-----Project A-----|
|--1.01-|-Approve Invoices--|--Jim--|---Dave---|
|--1.02-|--SCC Agreement---|--Jim--|---Victor--|
|-----------------------------------------------------------------|
|-----------------------blank row---------------------------------|
|-----------------------------------------------------------------|
|---2----|-----Project B-----|
|--2.01--|--Planning meeting-|--Jim--|---Victor--|
|--2.02--|-Database update--|--Jim--|---Victor--|
|--2.03--|-Master agreement-|--Jim--|-Victor, Dave-|
|-----------------------------------------------------------------|


This macro should do rename Sheet2 as 'Dave' and it'd contain:
|-----------------------------------------------------------|
|-A-|-B-|--------C--------|--K--|
|-----------------------------------------------------------|
|---1---|----Project A-----|-----|
|--1.01-|-Approve Invoices-| Jim |
|-----------------------------------------------------------|
|------------------------blank row--------------------------|
|-----------------------------------------------------------|
|---2---|----Project B------|-----|
|--2.03-|-Master agreement-| Jim |
|-----------------------------------------------------------|

Then it should rename Sheet3 as 'Victor' and it'd contain:
|-----------------------------------------------------------------|
|-A-|-B-|--------C----------|---K---|
|---1---|-----Project A------|-------|
|--1.02-|--SCC Agreement---|--Jim--|
|-----------------------------------------------------------------|
|-----------------------------------------------------------------|
|---2----|-----Project B-----|---K---|
|--2.01--|--Planning meeting-|--Jim--|
|--2.02--|-Database update--|--Jim--|
|--2.03--|-Master agreement-|--Jim--|
|-----------------------------------------------------------------|


...and it should do the same for any names in the cells of that column, and each cell can contain up to 3 names separated by a comma. Once I run this macro again it should update the information of each sheet created.

I'm new to macros-vba. I don't understand it much, but I found this code online :

Please Login or Register  to view this content.

And I tried this one:

Please Login or Register  to view this content.

I tried this other one:

Please Login or Register  to view this content.

And I just tried this last one:

Please Login or Register  to view this content.

I'm new to Excel macros-vba, I'm trying to modify any of this codes to work for my problem, but I cannot seem to achieve it. Also, if the code encounter in a the cell more than one name (e.g. Victor,Mike,Jim) instead of copying that row of information to their respective individual sheets, it creates a sheet with those names, and pastes all information there, and that's not what I want.


I'm attaching the file of the Task List itself for your assistance in helping me.

Thank you all in advance. I need your help.

Victor

Hi

See how this goes.

Please Login or Register  to view this content.

Put both items into a general module, make sure you are on sheet1, and run the sub aaa.


rylo

Hi,

First of all, I wan to thank you Rylo for your help, your code is the closest to what I need. Thank you for your response and your help.

The code does what it's supposed to do, but I think it needs to be tweaked a little bit for the format of my Task list.

I would like to ask you if is there anyway this code can be tweaked so the only A,B,K and L are displayed in the sheet for each person, and also to copy the colors of the main Sheet1
Also I'm using a Task List in a little different format and the code has some problems with it, when I run it again instead of updating the sheets, it adds the same information below it.


If you (or anyone) could help me to tweak this code to achieve what I just mentioned I'd truly appreciate it.

Thank you Rylo.

I'm attaching the newer Task list (without the macros code for file size attachment limit)

Hi

Try this one. IT takes a little while to run (thanks to the formatting), but I've put a counter on the status bar so you can see how it is going.

rylo

Rylo,

I can't thank you enough ..... the code works perfectly. You are the best.

Where nobody seemed to help me, you have helped me, I appreciate it.

However I did make a mistake in my last post, in these sheets I also need Column C on each of the individual sheet and its header 'Project/Task'.


I didn't want to bother you again with a mistake made by me, so I tried to modify it myself by changing the code to this:

Please Login or Register  to view this content.

However it seems is not that easy, because when I run this I lose some of the "ID's" (I lose all ID regarding 1. and 2.) in the individual sheets.

So I wanted to ask you if you could help me again, I apologize for my wrong post.

Also I wanted to ask you if there was a way to update these sheets when I run this program again, like some way of clearing all these sheets before the code is ran. So when it runs again, the new information is created in the blank sheets, instead of just piling the new info under the previous data.


Regards,


Victor

Victor

Updated to the output arrangements (you only missed out on changing the line

Please Login or Register  to view this content.

) and put in some code to clear out any existing data.

However, the ID isn't missing it out - it doesn't exist on the row for the item being copied. It looks like some sort of hierarchy and hasn't been filled.

You could take the integer of the number being put into column B and use that to fill column A rather than trying to copy across that non existing data.

rylo

Please Login or Register  to view this content.

Thank you Rylo for all your help.

Since your las post I've been trying to modify your code a little bit so I could get the output I desired.

However as much as I tried there is one thing I cannot do, and I want to ask your help on that.

When I run your macro I get the main header but I cannot the the headers for the subprojects. And that's very important, so I get 2.XX together with 3.xxx, and I would like to ask you if you could help me *** something to your code to make it copy also the subprojects headers.

This is the code as I have it now:

Please Login or Register  to view this content.

Also I tried to modify it so it updates it when I run it again, but for some reason when I run it again it works, but the main header gets deleted.

Thank you for your help Rylo.

Regards

Hi

Here's a bit more but I'm not sure I'm bringing in all the items you want.

Can you please put up an example output file with say one subsheet(Jim) and enough data completed as you require to allow me to see what exactly you are chasing.

Please Login or Register  to view this content.

rylo

Rylo,

that last code you posted was exactly what I needed, because it also copies all the the rows with the names: "Project A", "Project B", etc.

As you mentioned, it'd be much much better if you could actually see what output I'm trying to get. So to start I have uploaded the entire Task List with all its macros in a couple of Host websites.

So you can Open it and run the macro (as it is right now) with all modifications, and see for your self what's going on.

I renamed this macro from aaa to "Creating_Individual_Sheets"

These are the links where I've uploaded the entire Excel File:

http://www.filetohost.com/257643

http://www.mediafire.com/?ta5w2wemzdw


Now you can run it and see the output.

Thanks to your code I've achieved 95% of what I needed, and what I still need to modify is only following:

1) As you can see there hidden and unhidden data now. This macros copies all data, hidden and Unhidden. What I need is that copies only what is unhidden (only what's visible), because I keep hidden stuff from the past that is not important, but that I might need later on.

2) There are rows called "Sub-Project A" , etc. These are not in all blocks, so I suppose they have to be added directly in the code everytime they are needed, I don't know how to do that, probably if you input those row, I could use the code to learn to do it.

3) After you ran it, change some of the values and run it again. It updated everything correctly, except that for some reason deleted the main header, I do not know why.


I appreciate all of your help Rylo.


Regards,

Victor

Victor

1) I've updated the code to only extract the non hidden rows. I've also changed the test for existing sheet count to be 6 not 1.

Please Login or Register  to view this content.

2) Sub projects are going to be a problem. I can't just extract the next row every time that I bring in a heading, it doesn't always exist. I'm using a test on column L for an assigned resource, and as this doesn't exist for a subproject, then it will be excluded. If I ignore that part, then I don't have an output sheet to receive the subproject. I've put in some code to do the copying, but commented it out. If you can think of a way to look for the subproject items when you are on a sheet......

3) Not sure. See if it happens again with this new code.

rylo

Rylo,

Sorry for the delay response.

This is just to thank you for your help.

Your code worked perfectly, and I learn a lot from it.

So again, thank you so much for all your help, I appreciate it.

Regards,

Victor

Hi Rylo/ Victor,

Can you share with me the final excel with the macros? I too have a similar requirement for task allocations.

Thanks in advance
Joe

Joe

Have to wait for Victor as the links don't seem to work and all the code I had is on the post.

Failing that, you could create a new post outlining your problem, and put in a link to this post saying it is a similar situation...

rylo

Rylo,
Can you help me?

Thanks,
Joe

Rylo,

Waiting for your assistance....

Joe