Copy Date to Column Macro

[Hadi]

hello experts,

I'll explain my structure first then I'll ask the question

I have a large static master file with approx. 300 projects inside. the
projects have the same template and the template has the following structure:

Project Name (Outline level1)
WBS Summary No.1 (Outline level2)
subtask1 (outline level3)
subtask2

WBS Summary No.2
subtask1
subtask2

 

[John]

hello experts,

I'll explain my structure first then I'll ask the question

I have a large static master file with approx. 300 projects inside. the
projects have the same template and the template has the following structure:

Project Name (Outline level1)
WBS Summary No.1 (Outline level2)
subtask1 (outline level3)
subtask2

WBS Summary No.2
subtask1
subtask2
.
.
WBS Summary No.9
subtask1
subtask2

the subtasks may differ from project to another but overall the WBS element
are the same.

What I need is to be able to copy start and finish dates for each of the WBS
Summary tasks to custom date fields. To be more specific, I need the WBS
Summary No. 1 start and finish to be copied to a Date1&2 field from outline
level1 (Project Name) to each one of the WBs summary tasks (outline level2)
. WBs Summary No.2 date fields need to be copied to Date3&4 , WBS No. 3 to
Date4&5 and so on

Right now, I recorded a macro to copy each one of the WBs Summary tasks to
the Project Name level (Outline Level1). i have to run the macro 9 times and
then use another macro to fill down from outline level1 to the WBS Summary
tasks. it is quite long of a process with the amount of records i have.

help is always appreciated

Hadi

Hadi,
Either I don't quite understand what you need or you are making this way
to complex. First of all, if this is a one-shot deal (i.e. you only need
to do this one time on a single master file), it seems that a few copy
and fill down operations should get the job done without any need for
VBA. Second, if you already have a recorded macro, why not go ahead and
use that process (you're already halfway there).

If you do need to use this process more than once and/or one of the
above methods just isn't adequate, then please elaborate a little more
on which fields are to be copied. Part of your descriptions sounds like
you only need to copy the Start and Finish fields from outline level 2
for each WBS group and another part of your description implies a whole
bunch of Start and Finish fields need to be copied.

John
Project MVP

 

[Hadi]

John,

I build this static master everymonth using the original subfiles. I
thought about copying the dates into the subfiles so I always have them but
this will add alot of overhead to the users file which they really do not
need to look at any of these fields.

Here is an example of what I have, this one of the projects I have (I only
show to Outlinelevel2). as you can see, each wbs summary task has its own
start and finish dates. the Construction(7) start field is what I want be
ablel to copy on a custom date field and i am just showing a sample here.
ideally, i'd like to have a column for Design(2) start & finish, Job
Estimate(4) start & finish and so on. the reason i need these columns is it
will help me in my sort functions. very often, i get a request to filter all
of the projects to show, for example, Project Name and only Design(2) and
Construction(7) underneath it. i then get asked to sort the list by
construction start date which is impossible to do unless i have a column that
show that and i then i can easily make my sort.

Construction Start Finish
(7) Start
Helms Install ID Gate Wiring 6/28/05 1/3/05 2/26/07
AJE Authorized(1) 6/28/05 1/3/05 2/16/05
Design(2) 6/28/05 2/16/05 6/28/05
Material ordered(3) 6/28/05 3/30/05 6/10/05
Job estimate(4) 6/28/05 3/31/05 5/12/05
Permits(5) 6/28/05 2/15/05 8/15/05
Construction Contract(6) 6/28/05 5/13/05 5/13/05
Construction(7) 6/28/05 6/28/05 11/30/06

I hope I am making it a little bit more clear

thanks

 

[John]

John,

I build this static master everymonth using the original subfiles. I
thought about copying the dates into the subfiles so I always have them but
this will add alot of overhead to the users file which they really do not
need to look at any of these fields.

Here is an example of what I have, this one of the projects I have (I only
show to Outlinelevel2). as you can see, each wbs summary task has its own
start and finish dates. the Construction(7) start field is what I want be
ablel to copy on a custom date field and i am just showing a sample here.
ideally, i'd like to have a column for Design(2) start & finish, Job
Estimate(4) start & finish and so on. the reason i need these columns is it
will help me in my sort functions. very often, i get a request to filter all
of the projects to show, for example, Project Name and only Design(2) and
Construction(7) underneath it. i then get asked to sort the list by
construction start date which is impossible to do unless i have a column that
show that and i then i can easily make my sort.

Construction Start Finish
(7) Start
Helms Install ID Gate Wiring 6/28/05 1/3/05 2/26/07
AJE Authorized(1) 6/28/05 1/3/05 2/16/05
Design(2) 6/28/05 2/16/05 6/28/05
Material ordered(3) 6/28/05 3/30/05 6/10/05
Job estimate(4) 6/28/05 3/31/05 5/12/05
Permits(5) 6/28/05 2/15/05 8/15/05
Construction Contract(6) 6/28/05 5/13/05 5/13/05
Construction(7) 6/28/05 6/28/05 11/30/06

I hope I am making it a little bit more clear

thanks

Hadi,
Sorry, it is not more clear. Unfortunately the more detailed example
only confused me more (it probably isn't showing up on my newsreader the
way you typed it in when you posted). I see no hierarchy and there
appears to be three columns of dates but I can't tell what they
represent. You mention only showing outline level 2 but I see a number
in parenthesis after each task name (?) and I have no idea what those
are. In other words, the whole thing got lost in translation.

You did mention a couple of other things however. First, if you could do
what you want by copying the dates into the user project files, why not
just do that? I don't see the "overhead" concern. There is no reason
this data should impact the users in any way and the only way they would
even see it is if they added those fields to the normal view they use.

Second, you mentioned that the reason you want to copy the dates is to
give you the necessary flexibility to do specific sorts. It isn't real
clear to me that any copied data is necessary to get the sorts you want.
Sorting can be combined with filtering and the Grouping feature can
often be used to develop specialized reporting. But until I can clearly
understand the hierarchy and file structure I can't say if any of these
approaches might work.

Tell you what, if you can e-mail me a coy of your Project file(s) and
fully explain what you want, maybe I can help you with a solution. If
the files have sensitive information, I can send you a macro that will
desensitize the files or you can download a similar macro from fellow
MVP, Jack Dahlgren's website at: http://masamiki.com/project/macros.htm
Look for Jack's macro called "scrub".

John
Project MVP
jensenj6atatcomcastdotdotnet
(remove obvious redundancies)