Moving cells to another sheet

[Klartigue]

Please see the attached document. I would like to move each highlighted row to its perspective sheet based on which broker it is. I can use this code to do that:

Sub MoveDatatosheets()
' Copy and paste bids into sheets

Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "X").End(xlUp).Row
For i = 4 To lastrow

If .Cells(i, "N").Value = "MorgStan/DeanWit-Trader" Then

With Range(.Cells(i, "A"), .Cells(i, "Y")).Select
Selection.Cut
Sheets("MS").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Blotter").Select
End With

End If

Next i

End With

End Sub

For the example above, I would not only like to move row 2 to the sheet labeled MS, but i would also like to move row 3 to that sheet as well. So If under a highlighted cell in D12 is the word cover, I would like the cover row to move to the same sheet as the highlighted row above it. How can I reference that?

And for example, in column D2, A17 and A18 do not have covers so in that case just move those rows to their perspective sheets. So if there is a cover, move the row above it as well as the cover row, but if there is no cover than just move the higlighted row?

Thanks for the help!

[Bob Phillips]

As everything is either highlighted or cover, doesn't that just mean move the lot?

[Klartigue]

yea it means to move the lot. So for example, Item A12 is going to the "MS" sheet. But i would also like the next row, A3 (aka the cover row) to travel with that. So if a highlighted item has a cover located below it, then the highlighted row and its cover travel together to their perspective sheet.

[Klartigue]

Any ideas of how to move the covers with the high bids?

[Klartigue]

I have starting working with something like this but it is not pulling the correct trades

Sub highlight()
'
' highlight Macro
'

Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "N").End(xlUp).Row
For i = 2 To lastrow

If Application.CountIf(.Range("E1").Resize(i), .Cells(i, "E").Value) = 1 And .Cells(i, "Y").Value = "rfusaro2@bloomberg.net" Then

With Range(.Cells(i, "A"), .Cells(i, "Y")).Select
Selection.Cut
Sheets("AAM").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Bid List").Select
End With
ElseIf Application.CountIf(.Range("E1").Resize(i), .Cells(i, "E").Value) = 2 Then

With Range(.Cells(i, "A"), .Cells(i, "Y")).Select
Selection.Cut
Sheets("AAM").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End With

End If
Next i
End With
End Sub

[Klartigue]

Actually see this one, I am staying consistent with my very first question..

Sub MKISSANE()
'
'

Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "N").End(xlUp).Row
For i = 2 To lastrow

If Application.CountIf(.Range("E1").Resize(i), .Cells(i, "E").Value) = 1 And .Cells(i, "Y").Value = "mkissane@bloomberg.net" Then

With Range(.Cells(i, "A"), .Cells(i, "Y")).Select
Selection.Cut
Sheets("MS").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Bid List").Select
End With
ElseIf Application.CountIf(.Range("E1").Resize(i), .Cells(i, "E").Value) = 2 Then

With Range(.Cells(i, "A"), .Cells(i, "Y")).Select
Selection.Cut
Sheets("AAM").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End With

End If
Next i
End With
End Sub

[Klartigue]

Ok this works to pull the correct items for mkissane@bloomberg.net to the "MS" sheet but it does not work to pull the covers. So my elseif statement is not working:

Sub MKISSANE()
'
'

Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "N").End(xlUp).Row
For i = 2 To lastrow

If Application.CountIf(.Range("D1").Resize(i), .Cells(i, "D").Value) = 1 And .Cells(i, "Y").Value = "mkissane@bloomberg.net" Then

With Range(.Cells(i, "A"), .Cells(i, "Y")).Select
Selection.Cut
Sheets("MS").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End With
ElseIf Application.CountIf(.Range("D1").Resize(i), .Cells(i, "D").Value) = cover Then

With Range(.Cells(i, "A"), .Cells(i, "Y")).Select
Selection.Cut
Sheets("MS").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End With

End If
Next i
End With
End Sub

[Klartigue]

Any ideas on how to tweek the above macro so that it pulls the correct "covers" that go with the items above them?