none
Copy Column value to another programtically RRS feed

  • Question

  • Hi, I am trying to figure out some simple code that will allow me to copy the row ID value into a spare Number field for circa 30,000 task rows.  If I do a simple copy&paste in Project by clicking on the column name and then copy and paste into the number field it takes about 10 seconds to do it.  If I try to do it with VBA it is taking 10 minutes! 

    My code is simply:-

    SelectTaskColumn Column:="ID"

    Edit Copy

    SelectTaskColumn Column:="Number1"

    Edit Paste

    Any ideas please?

    Thanks

    Steve

    Wednesday, January 3, 2018 5:08 PM

All replies

  • Steve,

    Your VBA code takes so long because it uses forground processing, that is, it is basically equivalent to you doing it manually. Try the following code and see if it works better for you.

    After first posting I begin to think that maybe the longer processing time is due to screen updating, so I revised the macro to turn it off before processing then back on after it's all done.

    Sub CopyIDs()
    Dim t As Task
    Application.ScreenUpdating = False
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            t.Number1 = t.ID
        End If
    Next t
    Application.ScreenUpdating = True
    End Sub

    Hope this helps.

    John


    • Edited by John - Project Wednesday, January 3, 2018 7:00 PM better idea
    Wednesday, January 3, 2018 5:21 PM
  • Hi John,  a belated thanks for your response..  I did try this suggestion but it just wasnt working so I had to move onto other work.  I will try again in due course.

    Thanks

    S

    Monday, January 8, 2018 2:06 PM
  • Steve,

    Yeah, I wondered where you went. To be honest I have found in the past that using background processing (the method I gave you) sometimes actually runs slower that foreground processing (the method you used), for some types of operations.

    When you get a chance, I'd try the following. Cut your 30,000 task file in half (i.e. just delete half the tasks). Now try both methods and take metrics on run time. Find which method runs faster and also how run time is affected by the number of task lines.

    John

    Monday, January 8, 2018 2:57 PM
  • thanks John, I'll try that,

    Cheers

    Steve

    Monday, January 8, 2018 3:19 PM
  • Steve,

    I'd be interested to know what you find, and perhaps others on this forum would also.

    John

    Monday, January 8, 2018 6:06 PM