Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally copy from multiple sheets to one sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally copy from multiple sheets to one sheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy data from multiple sheets into new sheet | Excel Worksheet Functions | |||
How do I copy setting from one sheet to multiple sheets in Excel? | Excel Discussion (Misc queries) | |||
Copy paste WkBk/sheet 1 to multiple wkbks/sheets | Excel Programming | |||
copy data in one sheet to multiple sheets in same workbook | Excel Worksheet Functions | |||
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions |