Help please in selecting range dependent on another range

[Guest]

Hi,

I have a workbook with a worksheet named "master", the data is similar to
this:

Name Surname Mon am Mon pm Tue am Tue pm
a a Y Y Y
b b Y Y Y
c c Y Y Y
d d Y Y
etc

I have to sort the data for each day and by surname and copy the "name" and
"surname" data into the worksheet of the corresponding "day" (this I can do).
I am not sure how to select the data in columns "Name" and "Surname" down to
the point where in the column of the chosen "(day name)" the last Y value is
present.

Taking the above example, if I sorted on "Mon am" then the data I would have
to select and copy is names and surname "a", "c", and "d". If I sorted on
"Tue am" then the name and surname would be "b" and "c".

Can anyone help me with the code needed to select the correct data?

Thanks

 

[Bob Phillips]

Mick,

Here is some code that does it all

Sub move()
Dim cLastRow As Long, cLastCol As Long
Dim cLast As Long
Dim i As Long, j As Long

With ActiveSheet
cLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
cLastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
For j = 3 To cLastCol
Worksheets(.Cells(1, j).Value).Cells(1, "A") = .Cells(1,
"A").Value
Worksheets(.Cells(1, j).Value).Cells(1, "B") = .Cells(1,
"B").Value
Next j
For i = 2 To cLastRow
For j = 3 To cLastCol
If .Cells(i, j).Value = "Y" Then
cLast = Worksheets(.Cells(1, j).Value).Cells(Rows.Count,
"A").End(xlUp).Row
cLast = cLast + 1
Worksheets(.Cells(1, j).Value).Cells(cLast, "A").Value =
_
.Cells(i, "A").Value
Worksheets(.Cells(1, j).Value).Cells(cLast, "B").Value =
_
.Cells(i, "B").Value
End If
Next j
Next i
End With
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob

Thanks for this, it's just what I needed and will save me a great deal of
work. All I have to do now is try and understand whats going on with
it...lol. I'm sure I'll be able to unravel the mystery and most importantly
learn from it.

I owe you 1.

Regards

Mick