Conditionally copy from multiple sheets to one sheet

[Pam]

Hi,

I have a workbook with several sheets for each employee. There is a segment
in the same section of each worksheet that contains a list of items with a
completed date. What I would like to happen is if the completed blank is
null anywhere in each list, that item with the due date to be placed on a
new worksheet - as a summary of all incomplete items for each employee. Is
this possible? I do not have any code to supply, as I don't even know where
to begin.

I would greatly appreciate any help with this.
Thanks in advance,
Pam

 

[joel]

The code below wil check every sheet in the workbook in the range "A115"
for a blnak cell and if it find one copies the endtire row to a new worksheet
called Blanks.

Sub findblanks()

Segment = "A1:d15"
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
NewSht.Name = "Blanks"
NewRowCount = 1
For Each sht In Sheets
If sht.Name <> "Blanks" Then
Set SearchRange = sht.Range(Segment)
For RowCount = 1 To SearchRange.Rows.Count
For ColCount = 1 To SearchRange.Columns.Count
If SearchRange.Cells(RowCount, ColCount) = "" Then
SearchRange.Rows(RowCount).Copy _
Destination:=NewSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
Exit For
End If
Next ColCount
Next RowCount
End If
Next sht
End Sub

 

[Pam]

Joel,

Thanks for the reply, but I can't get this to completely work. It will add
the new sheet, but it won't add the data from any of my sheets where the
date is missing in a column. I tried stepping thru the code and it will
enter a line that is filled gray like the lines are in the worksheets, but
there is no text. As I continue stepping thru code, it removes the line,
will insert it again and then remove until the end of code where I have a
completely blank worksheet.

I would like for it to gather all the lines from the same segment specifed
in each worksheet that do not have a date completed in a column of the
segment and then place all those on one sheet. Like this:

sheet1
name task due completed
emp1 activity 1/1/09 1/15/09
emp1 activity 2/1/09

sheet2
emp2 activity 3/1/09
emp2 activity 4/1/09 4/15/09

sheet"Blanks" would be
emp1 activity 2/1/09
emp2 activity 3/1/09

Do you have any other suggestions as to how I can make this work?
Thanks again,
Pam