Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Sum a Range Dependent on Non-zero Entries | Excel Discussion (Misc queries) | |||
SUM a range dependent on dates | Excel Discussion (Misc queries) | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
How do I count data in range A:A that is dependent upon criteria . | Excel Worksheet Functions | |||
Selecting a Range inside a range | Excel Programming |