Conditionally copy from multiple sheets to one sheet
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
"joel" wrote in message
...
The code below wil check every sheet in the workbook in the range "A1:D15"
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" wrote:
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
|