none
Copying and Pasting Columns on Projects with Subprojects (BUG) RRS feed

  • Question

  • Dear Fellows,

    I've been having trouble with copying and pasting columns on Master Projects. Every time I try to do it, all the "Start" and "End" dates of my project get messed. Project automatically sets the 'End Date' = 'Start Date' for every Summary Task of my Subproject.

    I am not sure what is causing this, and since copying and pasting columns is a crucial part of a very important Macro I use, this issue is causing me a lot of stress lately.

    Thank you very much. I am sending also a picture that shows what happens to my SubProject dates when I try to Copy and Paste collumns on the Master Project.

    Hope any one of you may be able to help me solving this issue. Thank you very much for you attention.

    Bye!

    NOTE1: The issue occours whenever I try to copy a column and paste its values to a different column. It doesn't metter if I do it via a VBA Macro or mannualy with CTRL + C and CTRL + V. I have already tried to "Special Copy - Only Values" and it doesn't solve the problem.
    NOTE 2: The issue does not occour on simple projects. Only if there is a inserted Subproject.

    Tuesday, May 24, 2016 7:43 PM

Answers

  • Joao,

    Yes, I knew it was a typo but I couldn't resist taking notice :-)

    Okay, I think we got sidetracked on figuring out how to compute the value for the summary rows and forgot we still need the DateDifference formula applied to each task row. That can be accomplished by creating another custom field with the formula:

    Number3=IIf([Actual Finish]<55000,[Duration],IIf([Current Date]<[Start],0,ProjDateDiff([Start],[Current Date])))

    Note: the "55000" value is one way of detecting a "NA" in a date field

    And also change the Number1 field to be:

    Number1=[Number3]

    To illustrate, I've created these custom fields in a sample file. I renamed the Number3 field as "Start to Current" to more clearly identify that it is a modified actual duration from the task start to the current date. I also renamed the Text1 field to identify that it is the "Expected Complete".

    Again I caution that this all based on task durations and does not relate to work.

    If this answers your question or is at least helpful, please consider marking my response as the answer or each helpful response with a vote.

    John

    • Marked as answer by Joao Rodarte Monday, June 13, 2016 5:32 PM
    Tuesday, May 31, 2016 9:54 PM

All replies

  • Coluna,

    First, sorry for the late response. The forum had problems for several days and posts were not appearing. This issue is now fixed.

    Your title indicates you think this is a bug. I disagree.

    Apparently you are attempting to do the copy/paste on a dynamic master (i.e. master with linked subprojects). I would not recommend doing that. Why? Because in a dynamic master the subprojects are not actually a part of the master, rather the master only contains pointers to each of the subproject files. When I tried a copy and paste, (Duration to Duration1), on a dynamic master I have with 7 simple subprojects, I got very strange results including conversion of most tasks to manual mode. When I tried it on another complex dynamic master with 3 subprojects, the copy/paste appeared to work without problems. What does that tell me? Copy and paste works generally works okay within a single file, but with the added complexity and fragile nature of a linked structure, copy and paste is hit or miss process, and will very likely contribute to corruption of the structure.

    My question to you is this. You indicate that the copy and paste process is an important part of a macro you use. What exactly does that macro do? My guess is there would be a much better method to do what you need in the macro without using copy and paste.

    John




    Saturday, May 28, 2016 2:05 AM
  • Dear John,

    First, thank you very much for your answer. The problem that I am having is exactly the same problem that you had. The copy/paste are changing my summary tasks to manual mode, what is causing me a lot of problems.

    Just to check if I understood correctly: You are suggesting that I abandon the use of copy/paste completely whenever I am working on a Dynamic Master. Is that correct?

    Now, answering your last question: My macro calculates the expected %complete for each task, summary task as well as for the entire project, based on the current date. So what it does is: 1) Update Project to Todays Date. 2) Copy the values. 3) Undo the Update. 4) Post the copied values into a "% expected" text column.

    Unfortunatelly, as I am only an intern, I can't change the way this project is managed. So this field "% expected" has to exist, and I is extremelly hard to be calculated by hand, so this Macro was my solution until I found this "copy/paste" problem when using subProjects. The macro works great when dealing with simple projects.

    What I need: 2 columns (%complete and %expected). The first one must be updated manually by the Project Manager uppon meetings, and the second must be calculated automatically every time a manager opens a subProject or the Master Project. My macro follows:

    Private Sub Project_Open(ByVal pj As Project)
        OptionsViewEx ProjectSummary:=True
        OutlineShowAllTasks
        UpdateProject All:=True, UpdateDate:=Now(), Action:=1
        SelectTaskColumn Column:="% Concluída"
        EditCopy
        Application.EditUndo
        SelectTaskColumn Column:="Texto11"
        EditPaste
        ViewApply Name:="Gráfico de Gantt"
    End Sub

    Thank you once again for all the insight and help! Hope you can help me resolving this issue.

    Monday, May 30, 2016 6:55 PM
  • Joao,

    Yes you understood correctly. Do not use copy/paste with a dynamic master.

    With regard to your macro, as I indicated there is likely a better way to accomplish what you want without using copy/paste and indeed after you explained what you are doing, there is a much better way. I would calculate the expected percent complete in code by using the DateDifference Method with the start date being the Actual Start and the finish date being the current date. Then the ratio of the calculated value divided by the task duration gives the percent expected complete as of the current date.

    If you need assistance in writing the macro to do the above, let me know.

    Hope this helps.

    John

    Monday, May 30, 2016 7:38 PM
  • Dear John,

    Unfortunatelly, this formula does not calculate the %expected correctly to the Summary Tasks. Here comes one counter exemple (Current date = 30/5):

    Name | Initial Date | Finish Date | %expected (correct) | %expected (using your formula)

    Summary Task | 01/03  | 31/06 | 6% | 49%

    SubTask1 | 01/03  | 01/03 | 100% | 100%

    SubTask2 | 05/06  | 15/06 | 0%     | 0%

    As you can see, the Sumary Task is only expected to be 6% completed, because only 1 (out of 16) day of expected work has passed. Using your formula, I would get ~50%, since it doesn't consider that in some days, no work is expected to be done in the Task.

    I have already tried it and turns out it does not work properly in Summary Tasks or in the Summary Project Line. You can check this logic by using the "Update Project" function.

    Do you perhaps have any other suggestion?

    Thank you very much for all the help.

    Monday, May 30, 2016 8:22 PM
  • Joao,

    Sorry, I forgot that you also wanted the calculation for summary lines. Percent complete for summary lines is computed as

    Summary % Complete = (sum of actual durations of subtasks)/(sum of durations of subtasks)*100%

    So, yes, you will need a separate formula for summary lines.

    Hope this helps.

    If you need additional help, please tell me what calendar you are using (i.e. workdays, start and stop times, etc.). Also what is your definition of a workday (default is 8 hours)?

    John
    Monday, May 30, 2016 11:31 PM
  • Dear John,

    I believe this formula will resolve my issue for good. Can you help me writing it? If possible, I would like to do it without any macro.

    How to I write "sum of actual durations of subtasks" and "sum of durations of subtasks" in a formula? I could't do it by my own, since the number of subtasks in each summary task may vary.

    Thank you very much, I feel we are close to resolving this thread, and I believe this is be very useful for other as there are many threads on this same subject (% expected).

    Thank you once again,
    João
    Tuesday, May 31, 2016 3:49 PM
  • One more thing: For me, it doesn't metter very much when ever you include or not weekends and holydays on the sum, since we do not have a very solid work agenda in my office. Both ways work great for me.

    Best Regards!

    Tuesday, May 31, 2016 3:51 PM
  • Joao,

    Normally you would not be able to do this with a custom field formula because in Project, unlike Excel, a custom field formula can only operate on data in each row (i.e. it cannot pull data from other rows). However, since you will be doing the calculation at summary level, you can do this with a set of three custom field formulas. The reason you can do it for summary lines is that you can select a rollup option for custom field group and summary rows as the sum. Here are the steps

    1. Create a custom field: Number1=[Actual Duration]

    2. Create a second custom field: Number2=[Duration]

    3. For each of the two custom fields select the rollup option for group and summary rows and set it for "sum"

    4. Create a third custom field: Text1=Format([Number1]/[Number2]*100,"##") & " %"

    5. For the third custom field set the group and summary row to "use formula"

    The following screen shot shows an example with the above formulas. I've left the Number1 and Number2 fields visible for illustration, you can hide them if you want. Note their values are in minutes since that is the way Project stores all time related data. You could also use an IIF statement in Text1 to only show the formula results for summary rows.

    One more thing I like to mention. Using Percent Complete is not a good measure of how the project is performing or should be performing. Percent complete is a duration based measurement and does not take into account the amount of effort required to do each task. Percent Work Complete is a much better measurement and IS based on the work content of the task. For example, let's say we have two tasks, each two days duration. The first task has a total work content of 8 hours, 4 hours each day. The second task has a total work content of 16 hours, 8 hours each day. Now assume at the end of the first day, the resource assigned to the first task was efficient and finish the whole task, (actual work 8 hr), whereas the resource assigned to the second task was on track and finished half the task, (actual work 8 hr). Project will show Percent Complete at 75%, however the Percent Work Complete is only 67%. Food for thought.

    Hope this helps.

    John


    • Edited by John - Project Tuesday, May 31, 2016 4:58 PM a little more to it
    Tuesday, May 31, 2016 4:42 PM
  • DEAR (not Dead) John,

    Thank you very much for all the insights that you gave me among this thread. I feel like improving a lot because of your brilliant tips!

    I will try to implement this until the end of the day, but don't really think this is going to be a challenge, as your solution is easy and elegant.

    Please, leave this thread open so I can give you feedback reggarding the implementation.

    Best Regards,

    João.


    Tuesday, May 31, 2016 5:22 PM
  • Joao,

    You're welcome but I can't say I like the part about me being dead!

    John

    Tuesday, May 31, 2016 6:57 PM
  • Dear John, it was a misstypo, sorry (lol)

    Your solution is not working properly in my project because the [Actual Duration] field is update according to the "% complete" field. So the "% expected" field have always the same value as the "% complete". What I want is a field that tells me how much work it was suposed to be complete, regardless of the field "% complete", so I can compare both.

    The [Duration] works great.

    Tuesday, May 31, 2016 7:41 PM
  • Joao,

    Yes, I knew it was a typo but I couldn't resist taking notice :-)

    Okay, I think we got sidetracked on figuring out how to compute the value for the summary rows and forgot we still need the DateDifference formula applied to each task row. That can be accomplished by creating another custom field with the formula:

    Number3=IIf([Actual Finish]<55000,[Duration],IIf([Current Date]<[Start],0,ProjDateDiff([Start],[Current Date])))

    Note: the "55000" value is one way of detecting a "NA" in a date field

    And also change the Number1 field to be:

    Number1=[Number3]

    To illustrate, I've created these custom fields in a sample file. I renamed the Number3 field as "Start to Current" to more clearly identify that it is a modified actual duration from the task start to the current date. I also renamed the Text1 field to identify that it is the "Expected Complete".

    Again I caution that this all based on task durations and does not relate to work.

    If this answers your question or is at least helpful, please consider marking my response as the answer or each helpful response with a vote.

    John

    • Marked as answer by Joao Rodarte Monday, June 13, 2016 5:32 PM
    Tuesday, May 31, 2016 9:54 PM
  • Dear John,

    Unfortunatelly, I wasn't able to try your lastest solution yet. Hope to find some time to do it in this week.

    As soon as I try, I give proper feedback, alright?

    Kind Regards.

    Monday, June 6, 2016 5:16 PM
  • Joao,

    I did wonder what happened to you. Glad to hear its still on your "to-do" list.

    John

    Monday, June 6, 2016 7:44 PM
  • Dear John,

    I implemented your solution successfully! Thank you very much.

    I am only having one problem: The check to verify if the field [Start] and [End] are filled are not working. Therefore, if I have any tasks without Start / End dates, the #ERROR message is shown in the task and in the respective summary tasks.

    Do you have any other solution to avoid this #ERROR? ideally, when ever we have tasks without [Start]/[End] dates, the %expected field should show "0%".

    Thank you!!

    Tuesday, June 7, 2016 5:09 PM
  • Joao,

    Do you have manually scheduled tasks in your plan? If so, then you need to detect a null text field ("") and set the percent complete to 0%.

    John

    Tuesday, June 7, 2016 5:52 PM
  • Dear John,

    Thank you! It's all working perfectly without any macros or hard tricks.

    How can I let everybody know how awesome you are?

    Monday, June 13, 2016 5:32 PM
  • Joao,

    You're more than welcome and thanks for the feedback. You've already offered your praise for my help through votes and mark as answer and it is appreciated.

    John

    Monday, June 13, 2016 7:38 PM
  • John,

    When I try to use your solution on big Master Projects with external links, I get some error messages. Do you know why?

    As you can see on the attached image, the auxiliar [Number] fields are calculated correctly, but the "% expected" field is using the formula properly and I get the "#ERROR" value for the Project Summary Line.

    Formula on the "%expected" (labeled as Real) field is as follows, and is working perfectly fine on the subProjects alone: 

    CInt(IIf([Número1]=0;0;IIf([Número3]=0;1;[Número1]/[Número3]))*100) & " %"

    PS: This error is kind of random. Sometimes it does not occour.

    PS2: I couldn't upload te image, because it says my account is not verified yet. Not sure what I am suposed to do to change that, since I have already verified it though my e-mail address.

    Thursday, June 16, 2016 4:12 PM
  • Joao,

    Okay, a master project file is a different file structure so structuring the custom fields to do what you want requires more effort. Here's why. In a dynamic master, which is the default, the subrprojects are not actually part of the master, rather, the master contains pointers to each of the subproject files. The only part of the subprojects that do actually belong to the master are the summary line insertion points for each subproject. You can readily see this by looking at the ID field. The ID sequence re-starts for each subproject however the summary line insertion points are part of the master ID sequence.

    What that means is this. If you customize fields in each subproject, that customized value will show when viewed in the master. If you also customize those same fields at master level, that customization will only apply to those fields at master level which will be none at all unless you select the option to use the customization for task and group summary rows.

    Does that help explain why it's not quite working for you?

    With regard to being verified, normally a users will be verified automatically after about 24 hours from their first forum post and since you are well beyond that, I'm surprised you still get the message about not being verified. If you still can attach an image, see the following:

    http://social.technet.microsoft.com/wiki/contents/articles/15960.how-to-verify-your-msdntechnet-forums-account-so-that-you-can-post-images-and-links.aspx

    John

    Friday, June 17, 2016 2:25 AM
  • Dear John, I understand.

    But why sometimes it works and sometimes it doesn't? It seems a little arbitrary to me.

    What configurations should I put on these fields if I want them to be calculated on the Master Project in the same way it is calculated on the subProjects?

    Wednesday, June 29, 2016 5:13 PM
  • Joao,

    Yeah, sometimes things do seem arbitrary but in reality they are not, however it's not always easy to figure out the underlying problem. Without actually seeing examples of when "it works" and when "it doesn't work" I wouldn't know what to tell you. Can you show me examples of each? You should be verified such that you can post some screen shots but a screen shot may not be able to show enough information. It's worth a try.

    With regard to what you want to show, it's been a couple of weeks since we last communicated on the forum and I've worked on many other issues during that time. I can't keep track of what everyone is doing so I need a refresher for your particular situation. If you can summarize what you have and what you want to do, it will be easier to help.

    John

    Thursday, June 30, 2016 12:16 AM
  • Dear John,

    First, let me apologize for taking so long to reply to you. The MSDN forum was banned by my IT Department so I have to bring my personal computer with me to get in touch with you. Today I managed to remove it from the banned sites and I can finally comunicate with you more often.

    My goal is to show the "% expected" on projects, wich you have already helped me with (and is working fine), and also on Master Projects, wich is not working fine.

    The solution you gave me was to calculate this "% expected" using 3 columns with formulas, in it works perfectly fine for tasks and summary lines, but when you Add the subprojects on a Master Project with External Link, it sometimes show an ERROR message on the Project Summary Lines.

    I tried to snap it to you on a screenshot, but as I told you, I can't upload it here because I am getting an error saying my account is not verified. Maybe I can send it to you email with a simple .mpp exemple?

    I would like to thank you once again for all the help that I got so far.

    Best Regards!
    João

    Monday, July 11, 2016 4:47 PM
  • Joao,

    Yes, perhaps a one-on-one will work better. My e-mail address is below.

    John

    jensenljatatfastmaildotdotfm

    (remove obvious redundancies and the 7th character is a letter)

    Monday, July 11, 2016 6:15 PM