Hello,
I use the following code to retrieve over due tasks that are not finished from different worksheets and add them to a summary page (see examples below).
Examples of sheets:
Each worksheet details tasks in a project.
"ERP Project" Worksheet
<table class="MsoNormalTable" style="width: 233.4pt; margin-left: 4.65pt; border-collapse: collapse;" width="311" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 13.5pt;"> <td style="border: 1pt solid windowtext; padding: 0cm 5.4pt; width: 125pt; height: 13.5pt;" width="167" nowrap="nowrap" valign="bottom"> ERP Project
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 125pt; height: 13.5pt;" width="167" nowrap="nowrap" valign="bottom"> Stage
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom"> Due Date
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom"> Finish Date
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 125pt; height: 13.5pt;" width="167" nowrap="nowrap" valign="bottom"> Examine Legacy System
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom"> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom"> </td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 125pt; height: 13.5pt;" width="167" nowrap="nowrap" valign="bottom"> Examine CSF
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom"> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom"> </td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 125pt; height: 13.5pt;" width="167" nowrap="nowrap" valign="bottom"> Evaluate Market Leaders
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom"> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom"> </td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 125pt; height: 13.5pt;" width="167" nowrap="nowrap" valign="bottom"> Report Findings
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom"> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom">
</td> </tr> </tbody></table>
"Renovation Project" Worksheet
<table class="MsoNormalTable" style="width: 242.75pt; margin-left: 4.65pt; border-collapse: collapse;" width="324" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 13.5pt;"> <td style="border: 1pt solid windowtext; padding: 0cm 5.4pt; width: 134.35pt; height: 13.5pt;" width="179" nowrap="nowrap" valign="bottom"> Renovation Project
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 134.35pt; height: 13.5pt;" width="179" nowrap="nowrap" valign="bottom"> Stage
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom"> Due Date
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom"> Finish Date
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 134.35pt; height: 13.5pt;" width="179" nowrap="nowrap" valign="bottom"> Examine Structural Damage
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom"> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom"> </td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 134.35pt; height: 13.5pt;" width="179" nowrap="nowrap" valign="bottom"> Examine Cost of Materials
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom"> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom"> </td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 134.35pt; height: 13.5pt;" width="179" nowrap="nowrap" valign="bottom"> Evaluate DIY v Pro
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom"> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom"> </td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 134.35pt; height: 13.5pt;" width="179" nowrap="nowrap" valign="bottom"> Report Findings
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom"> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom">
</td> </tr> </tbody></table>
What I want to be able to do automatically is to populate my "Summary" Worksheet with all overdue tasks from these projects like so.
"Summary" Worksheet, Date = Today() here and used to find tasks that are not finished but overdue.
<table class="MsoNormalTable" style="width: 234.05pt; margin-left: 4.65pt; border-collapse: collapse;" width="312" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 12.75pt;"> <td colspan="4" style="padding: 0cm 5.4pt; width: 234.05pt; height: 12.75pt;" width="312" nowrap="nowrap" valign="bottom"> Projects with overdue tasks
</td> </tr> <tr style="height: 13.5pt;"> <td style="padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom"> Date
</td> <td style="padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom"> </td> <td style="padding: 0cm 5.4pt; width: 63.6pt; height: 13.5pt;" width="85" nowrap="nowrap" valign="bottom">
</td> <td style="padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border: 1pt solid windowtext; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom"> ERP Project
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; width: 63.6pt; height: 13.5pt;" width="85" nowrap="nowrap" valign="bottom">
</td> <td style="padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom"> Stage
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom"> Due Date
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 63.6pt; height: 13.5pt;" width="85" nowrap="nowrap" valign="bottom"> Finish Date
</td> <td style="border: medium none ; padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom"> Report Findings
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom"> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 63.6pt; height: 13.5pt;" width="85" nowrap="nowrap" valign="bottom">
</td> <td style="padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 63.6pt; height: 13.5pt;" width="85" nowrap="nowrap" valign="bottom">
</td> <td style="padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 63.6pt; height: 13.5pt;" width="85" nowrap="nowrap" valign="bottom">
</td> <td style="padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom"> Renovation Project
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 63.6pt; height: 13.5pt;" width="85" nowrap="nowrap" valign="bottom">
</td> <td style="padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom"> Stage
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom"> Due Date
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 63.6pt; height: 13.5pt;" width="85" nowrap="nowrap" valign="bottom"> Finish Date
</td> <td style="padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom"> Report Findings
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom"> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 63.6pt; height: 13.5pt;" width="85" nowrap="nowrap" valign="bottom">
</td></tr></tbody></table>
The problem is my code only brings back one over due task (the last overdue) from each sheet.
Any ideas how I can fix this?
Thank you.
I use the following code to retrieve over due tasks that are not finished from different worksheets and add them to a summary page (see examples below).
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ray As Variant
Dim Ray2 As Variant
Dim Ws As Worksheet
Dim Rng As Range, Dn As Range
Dim Lst As Long
Dim c As Long
ray = Array("Project with Overdue Date", "", "Date", "=Now()")
Ray2 = Array("Stage", "Due Date", "Finish Date")
With Sheets("Summary")
.Columns("A:C").ClearContents
.Range("A1:b1") = Application.Index(ray, 1, Array(1, 2))
.Range("A2:B2") = Application.Index(ray, 1, Array(3, 4))
.Range("B2").NumberFormat = "dd/mm/yyyy"
End With
For Each Ws In Worksheets
c = c + 1
Lst = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Lst = Lst + IIf(c > 1, 1, 0)
If Not Ws.Name = "Summary" Then
With Ws
Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
If Dn <> vbNullString And Dn(, 3) = vbNullString And Dn(, 2) < Date Then
With Sheets("Summary").Range("A" & Lst)
.Value = Ws.Name
.Offset(1).Resize(, 3) = Ray2
.Offset(2) = Dn
.Offset(2, 1) = Format(Dn.Offset(, 1), "dd/mm/yyyy")
End With
End If
Next Dn
End With
End If
Next Ws
End Sub
Examples of sheets:
Each worksheet details tasks in a project.
"ERP Project" Worksheet
<table class="MsoNormalTable" style="width: 233.4pt; margin-left: 4.65pt; border-collapse: collapse;" width="311" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 13.5pt;"> <td style="border: 1pt solid windowtext; padding: 0cm 5.4pt; width: 125pt; height: 13.5pt;" width="167" nowrap="nowrap" valign="bottom"> ERP Project
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 125pt; height: 13.5pt;" width="167" nowrap="nowrap" valign="bottom"> Stage
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom"> Due Date
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom"> Finish Date
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 125pt; height: 13.5pt;" width="167" nowrap="nowrap" valign="bottom"> Examine Legacy System
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom">
01/09/2010
02/09/2010
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom">
10/09/2010
10/09/2010
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom">
25/09/2010
24/09/2010
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom">
30/09/2010
</td> </tr> </tbody></table>
"Renovation Project" Worksheet
<table class="MsoNormalTable" style="width: 242.75pt; margin-left: 4.65pt; border-collapse: collapse;" width="324" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 13.5pt;"> <td style="border: 1pt solid windowtext; padding: 0cm 5.4pt; width: 134.35pt; height: 13.5pt;" width="179" nowrap="nowrap" valign="bottom"> Renovation Project
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 134.35pt; height: 13.5pt;" width="179" nowrap="nowrap" valign="bottom"> Stage
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom"> Due Date
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom"> Finish Date
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 134.35pt; height: 13.5pt;" width="179" nowrap="nowrap" valign="bottom"> Examine Structural Damage
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom">
01/09/2010
02/09/2010
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom">
10/09/2010
10/09/2010
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom">
25/09/2010
24/09/2010
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom">
29/09/2010
</td> </tr> </tbody></table>
What I want to be able to do automatically is to populate my "Summary" Worksheet with all overdue tasks from these projects like so.
"Summary" Worksheet, Date = Today() here and used to find tasks that are not finished but overdue.
<table class="MsoNormalTable" style="width: 234.05pt; margin-left: 4.65pt; border-collapse: collapse;" width="312" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 12.75pt;"> <td colspan="4" style="padding: 0cm 5.4pt; width: 234.05pt; height: 12.75pt;" width="312" nowrap="nowrap" valign="bottom"> Projects with overdue tasks
</td> </tr> <tr style="height: 13.5pt;"> <td style="padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom"> Date
</td> <td style="padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom">
02/10/2010
</td> <td style="padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border: 1pt solid windowtext; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom"> ERP Project
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; width: 63.6pt; height: 13.5pt;" width="85" nowrap="nowrap" valign="bottom">
</td> <td style="padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom"> Stage
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom"> Due Date
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 63.6pt; height: 13.5pt;" width="85" nowrap="nowrap" valign="bottom"> Finish Date
</td> <td style="border: medium none ; padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom"> Report Findings
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom">
30/09/2010
</td> <td style="padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 63.6pt; height: 13.5pt;" width="85" nowrap="nowrap" valign="bottom">
</td> <td style="padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 63.6pt; height: 13.5pt;" width="85" nowrap="nowrap" valign="bottom">
</td> <td style="padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom"> Renovation Project
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 63.6pt; height: 13.5pt;" width="85" nowrap="nowrap" valign="bottom">
</td> <td style="padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom"> Stage
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom"> Due Date
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 63.6pt; height: 13.5pt;" width="85" nowrap="nowrap" valign="bottom"> Finish Date
</td> <td style="padding: 0cm 5.4pt; width: 4.95pt; height: 13.5pt;" width="7" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 106.35pt; height: 13.5pt;" width="142" nowrap="nowrap" valign="bottom"> Report Findings
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 59.15pt; height: 13.5pt;" width="79" nowrap="nowrap" valign="bottom">
29/09/2010
</td></tr></tbody></table>
The problem is my code only brings back one over due task (the last overdue) from each sheet.
Any ideas how I can fix this?
Thank you.