Help please in selecting range dependent on another range

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
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
 
Back
Top