Looping macro

I am trying to get data from Sheet Project_Pipeline Column D1, Match it to Row A1 in the DayView and continue until D1 is empty. I can only get it to copy and paste 1 section of data.

Please Login or Register  to view this content.

Can you post a sample of your xls?

I'm having trouble opening the attachment .Can you zip it and repost it?

Hi again
I little confused at what you are trying to achieve.

I am trying to get data from Sheet Project_Pipeline Column D1, Match it to Row A1 in the DayView and continue until D1 is empty. I can only get it to copy and paste 1 section of data.

For each of the dates in the dayview in Row 1 , Do you want to search thorugh the pipeline sheet column D and find a matching Date then if the date matches copy the corresponding persons name to the next available cell below the date your are searching for? in the Dayview?
After finding the corresponding names for the dayview date in A1 move to B1 and search and copy as you did for the A1 date? then continue to with the same process for all dates up to cell N1 in the dayview

If it is not this can you please explain step by step??

Try this:
Put in a module.

Please Login or Register  to view this content.

I will have a look

Sorry but I am confused as to what you want.
Cell C14 on the the dayview is a category.
If for Example the category is business do you want to color all the "Business" cells in the project _pipeline sheet.
But what color corresponds to "Business"????
For all the other cells that are not "Business"...these are not colored?? Correct?

I am also not sure what the relationship between the "Activity color key" colors and the the data on the project pipeline sheet??? Please can you explain?

Try this:

Please Login or Register  to view this content.

I'm still confused ..which columns do u want to copy the Project_Pipeline H, I , J and K in the DayView ?
Make you can put some comment in the spread sheet to illustrate ?

I think I am starting to understand what you want.
In Essence you want to add a record in the dayview multiple times relative to if it is memebr of each category..business,national support etc as shown in columns H,I J, K. for the rocrd

Previously the macro would just the record once without category in column C
But you want to duplicate the same record each time it has Consumer, Business Employee , National Sales Support in the columns H-K ? Correct or No?

Give this one ago.
(You should remove the text you wrote for me from before running this or it will format column A with a huge width!

Please Login or Register  to view this content.

Can you explain in what way.???
Remember that you ahve autofilters there.. so You need to se all in columns c and d to show all records.

When you made some changes it stopped working ....
Try with this..

Please Login or Register  to view this content.

See attached

Ok but changing this will screw up the previous code.
Here is another thought.
why not just create a dropdown for all the cells and then you can select from drop down Consumer Business Employee National Sales Support in Columns H-K?

ok this can be done.

something like this should do it.

Please Login or Register  to view this content.

Note: If you are going to have headers in row 1 of you pipline sheet then you need to change the highlighted value in the previous sub I sent you from 1 to 2

Please Login or Register  to view this content.

Sorry I had it working in the revers direction.

try this one

Please Login or Register  to view this content.

Sorry I completely confused.
This is what it did before you you commented out some of the stuff and made some other changes.

Please see the attached and run this and you should see it works.
The only change from the original is the extension of th categories of H-K to H-L.
Please paste the macro from this sample book into your workbook and trash the old one as you have introduced some bugs into the old one.
HTH
Steve

This is what really it is doing ..l will explain below
But firstly...as I understand it from you description what you want now will create something circular to what you already have and quite frankly doesn't make sense...as it will not provide you any more than the macro is already giving. In fact using Dayview Col C as you have it now it the " the selection criteria" to search the pipeline sheet will cause lots of issues...
as
a) you don't know from the Dayview what link is betwee "LOB" date and person is.
b) you don't know the number rows need for each "LOB" in the dayview
c) etc.

Defining the problem
Remember your source data is the project_pipeline sheet.
1/ you have series of dates that you want to bring corresponding data across.
2/ For each instance of the date and the "LOB" (Cols H-L) in the pipeline sheet you want a create a new row in the dayview.
You populate the new row in the dayview with the name in the correct date column and the "LOB" in LOB column.(col C)


Explanation of how the macro works
For each Date in Dayview Row 14 it is looking in the PRJ_PL sheet for matching dates.
If it finds a matching date then it looks at whether in Col H-L if there is a entry.
If there is an entry if creates new row in the Dayview with the Name from PRJ_PL Col C and puts it in the matching date column. and adds the "LOB" eg."Consumer" to Dayview Col C.
For Example.
starting with 10-04-2008 ( Dayview Col D Rw 14)

It will find the first entry 9534
It will firstly create a row with "BOB" in col D and "Consumer" In dayview Col C
Then it will create a row with "Bob" in Col D and "National Sales Support in Dayview Col C
Then it will create a row with "Bob" in Col D and "Phone" in Dayview Col C
After this it will look for the next match of the date 10-04-2008
If no other matches for this date ( 10-04-2008 are found it moves to the next date ( Dayview Col E RW14).... and continues as before.

SORTING/FILTERING to Group by LOB
So If you want to see the dayview sheet by sorted by LOB
the you can sort the rows by "LOB"
alternatively use and autofilter
...eg..Consumer then use an auto filter From C14 down to the last row. c65336
Then just click on C14 and select the "LOB" you want "Consumer"

Steve

Understand? Are you ok with it now?