Help please in selecting range dependent on another range
 

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

Help please in selecting range dependent on another range
 

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)


"MickJJ" wrote in message
...
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

Help please in selecting range dependent on another range
 

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

"Bob Phillips" wrote:

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)


"MickJJ" wrote in message
...
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