Problem trying to generate a report with a formula

[billosullivan]

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

 

[Tom Ogilvy]

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.