Create a lessons learnt file

[mesteptoe]

I manage a number of projects which Project Managers run. I have created a Project Management System based on Excel. One element of this involves the PM's completing a Table for their individual project entitled "Lessons learnt". There could be up to 40 separate workbooks and within each is a "Lessons learnt" log. I would like all their comments to be transferred to a master "Lessons learnt" log, i.e. a compilation from each workbook. PM's could then look through the Master file and, hopefully, it will improve future projects.

What I need to be able to do automatically is pick up relevant cells in each individual workbook take them to the Master and drop them in the next available blank cells in the Master file. I assume this would be done by the PM pressing a button (Macro) in an individual file to take it over to the Master. The Master would be filed in a similar location but not necessarily open. So it would have to open, take in the data in the next available blank cells, and then be saved before closing again.

Any help much appreciated.

Thank you.

Graham

 

[mesteptoe]

So far this seems to have generated a blank. Can anhyone help please.

Thank you.

Graham

 

[ColinKJ]

Hi Graham,

I can probably be able to help, but I not until this evening.

Can you post an example of the PM data to want identified and copied, and an example of how you see the Master data.

If anyone else can help Graham in the meantime, please do.

 

[mesteptoe]

Thank you for responding Colin.

Each project will include the following section,

<TABLE style="WIDTH: 211pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=281 border=0><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=75 height=17> </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 155pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=206 colSpan=2>Lessons learnt from this project</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1 (Cell A1)</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" colSpan=2> Cell B1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2 (Cell A2)</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" colSpan=2> Cell B2</TD></TR></TBODY></TABLE>

There could be up to 40 separate pieces of information like this, created by Project Managers' and saved in separate Project Workbooks.

I need to pick up information in cells A1, B1, A2, B2 etc. together with the name of the Project Manager, the Project and Project Completion Dates (In other cells in each Workbook). These need to load into a Master Log, e.g.

<TABLE style="WIDTH: 383pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=510 border=0><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 172pt; mso-width-source: userset; mso-width-alt: 8374" width=229><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=75 height=17>PROJECT</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 80pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=106>Project Manager</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 75pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=100>Date completed</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 172pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=229>Lessons learnt from this project</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> Project X</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> PM X</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> Date X</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> From Cell B1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> Project Y</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> PM Y</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> Date Y</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> From Cell B2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

Each time the PM presses a Button (Macro) to send data to the Master log Workbook I need the data to be saved into the next available blank cells.

Hope you understand and can help.

Regards

Graham

 

[ColinKJ]

Hi Graham,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks for posting the information.
<o></o>
<o></o>
First, because the best solutions are always the simplest, would it not be possible / preferable, for the Project Managers to enter the information directly in the Master Workbook to be shared by all Project Managers?.<o></o>
<o></o>
Assuming for a moment that this is not a “goer”, I assume the 40 Workbooks you refer to are each a separate Project?.

<o></o>
<o></o>
How often during a Project are the "Lessons learnt from this project" to be saved to the Master WB?

<o></o>
I assume the Master WB is saved in a location that all PMs have access to?.
<o></o>
Is the Project Name & PM's name available in the Project WB, if so where is it stored in the WB.

<o></o>
Is the "Lessons learnt from this project" information in each Project WB stored on the same Worksheet in each Project WB?. Also, on which Worksheet in the Master WB is the Information stored?.

<o></o>
<o></o>
Sorry about all the questions, just want to come up with the best solution.<o></o>

 

[mesteptoe]

Thank you for your time in considering this. Following your suggestion it seems that the easiest solution might well be to allow Project Managers to access and insert comments in a common Lessons Learnt workbook. I'll try that before pursueing the other option.

Thanks again.

Regards

Graham