Sub ABC()
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim rng As Range, rng1 As Range
Dim cell As Range, cell1 As Range
Dim cell2 As Range
Set sh1 = Worksheets("Report") '<== Change
For Each sh In Worksheets
If sh.Name <> sh1.Name Then
Set rng = sh.Range(sh.Cells(2, 1), _
sh.Cells(2, 1).End(xlDown))
For Each cell In rng
Set rng1 = cell.Offset(0, 1).Resize(1, 7)
For Each cell1 In rng1
If cell1.Value = "Yes" Then
Set cell2 = sh1.Cells(Rows.Count, _
cell1.Column - 1).End(xlUp)(2)
cell2 = cell.Value
End If
Next
Next
End If
Next
End Sub
worked for me.
Change "Report" in this line
Set sh1 = Worksheets("Report")
to reflect the name of the report sheet.
Test this on a copy of your workbook.
--
Regards,
Tom Ogilvy
<billosullivan[ at ]gmail.com> wrote in message
news:1159634719.499390.323340[ at ]c28g2000cwb.googlegroups.com...
[Quoted Text] > Hi all,
>
> I'm trying to generate a report and am having trouble with the formula.
>
> Any help would be greatly appreciated.
>
>
> Here is what I need to be able to do.
>
>
> I have 6 worksheets in the same workbook, worksheets 1 - 5 are
> identically laid out and the sixth is the one I want to generate a
> report on. Column A is for peoples names, Columns B-H are labelled
> Monday - Sunday on top. If the person in A2 is working on Monday then I
>
> would type "Yes" into B2 and so on for the rest of the week and for the
>
> rest of the rows also. It is the case that not everyone will work
> everyday, therefore I want the report page to be a kind of roster
> report detailing who is working on what days. The report page is laid
> out as follows. Weekdays in A1-G1. What I want to do is have worksheets
>
> 1-5 checked and for instance bring back everyone's name who is
> working on Monday to worksheet 6 A2-XX (XX being whatever the last
> individuals name working that day is) I would ideally like the names to
>
> be sequential as there are 40 people that could potentially be working
> but only 15-20 may work on the Monday (I don't want the report to be
> spread over 41 rows if possible)
>
>
> I hope I have explained this well enough. Any help you might be able to
>
> give at all would really be appreciated! I've started pulling my hair
> out trying it at this stage : -)
>
>
> Thanks again!
>
>
> Bill
>
|