Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 56
Default Conditional copy from multiple sheets to one sheet

Hi,

Please forgive the repost, but I'm hoping someone will please follow-up with
a solution to the problem.

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.

An example:

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.

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

Joel supplied the following code, but I can't get it to completely work -
the new sheet will add to workbook, but no lines are copied.
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


Thanks in advance for any help.

Pam



  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Conditional copy from multiple sheets to one sheet


Well, I tried this code and didn't have much success with it either. I
have all of the options on to make sure that variables are declared and
such (VERY important), so I got numerous errors until I changed it to
this:
Sub findblanks()

Dim Segment As Range, NewSht As Excel.Worksheet
Dim NewRowCount As Integer, sht As Excel.Worksheet
Dim SearchRange As Range
Dim RowCount As Integer, ColCount As Integer

Set Segment = Range("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 = 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

Not sure why people don't want to Dim their vars, but even that didn't
work like you are wanting (at least I don't think so). It filled out the
entire worksheet with the same sets of data for some reason. I'll have
to look into it some more to figure out why...


--
gmorris
------------------------------------------------------------------------
gmorris's Profile: http://www.thecodecage.com/forumz/member.php?userid=245
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91772

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 56
Default Conditional copy from multiple sheets to one sheet

Thank you, gmorris, for the prompt reply and help. I greatly appreciate it
as I've spent a great deal of time trying to make this work.

Thanks again,
Pam

"gmorris" wrote in message
...

Well, I tried this code and didn't have much success with it either. I
have all of the options on to make sure that variables are declared and
such (VERY important), so I got numerous errors until I changed it to
this:
Sub findblanks()

Dim Segment As Range, NewSht As Excel.Worksheet
Dim NewRowCount As Integer, sht As Excel.Worksheet
Dim SearchRange As Range
Dim RowCount As Integer, ColCount As Integer

Set Segment = Range("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 = 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

Not sure why people don't want to Dim their vars, but even that didn't
work like you are wanting (at least I don't think so). It filled out the
entire worksheet with the same sets of data for some reason. I'll have
to look into it some more to figure out why...


--
gmorris
------------------------------------------------------------------------
gmorris's Profile: http://www.thecodecage.com/forumz/member.php?userid=245
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91772



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditionally copy from multiple sheets to one sheet Pam[_3_] Excel Programming 2 April 29th 09 10:30 PM
Copy data from multiple sheets into new sheet Angie Excel Worksheet Functions 0 June 5th 07 10:53 PM
How do I copy setting from one sheet to multiple sheets in Excel? danam6 Excel Discussion (Misc queries) 1 April 27th 06 09:05 AM
Copy paste WkBk/sheet 1 to multiple wkbks/sheets wrpalmer Excel Programming 1 August 20th 05 03:08 PM
copy data in one sheet to multiple sheets in same workbook BrianMultiLanguage Excel Worksheet Functions 4 July 27th 05 07:26 PM


All times are GMT +1. The time now is 12:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"