Previous Topic Next Topic

Conditional transfer of rows to another sheet

Closed
Ash - Jan 31, 2011 at 12:09 PM
 LP - Feb 21, 2013 at 03:37 PM
Hello,

I am working on a production worksheet. Sheet1 is called "Lead Input" and contains columns of names, addresses, prices, etc. There are two columns that are important for the code that I need, columns C and F. These columns are for the assigned agent (C) and the status of the job (F).

First, I have 5 other worksheets, one for each agent. So dependent upon cell content in column C (agent names), I need that row to be copied to the specific agent's worksheet as well at the next available row once I have entered it in on Sheet1.

In addition to this, I have a worksheet for "Closed Production," and I need all rows with the status "closed" (column F) to be copied onto that worksheet.

So I need Sheet1's content to be directed to the proper agent sheet, as well as copied over to "closed production" when that status is met.

I'm relatively new to Excel, so any help would be greatly appreciated. Thanks!

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Feb 1, 2011 at 10:28 AM
Hi Ash,

Try this code: 
Sub test()
Set mrNames = Sheets("Lead Input").Range("C2:C10")
Set mrStatus = Sheets("Lead Input").Range("F2:F10")

For Each cell In mrNames

If cell.Value = "00 Agent" Then
cell.EntireRow.Copy
Sheets("00 Agent").Range("C" & Rows.Count).End(xlUp).Offset(1, -2).PasteSpecial
    End If
If cell.Value = "Personal Agent" Then
cell.EntireRow.Copy
Sheets("Personal Agent").Range("C" & Rows.Count).End(xlUp).Offset(1, -2).PasteSpecial
    End If
If cell.Value = "Cyber Agent" Then
cell.EntireRow.Copy
Sheets("Cyber Agent").Range("C" & Rows.Count).End(xlUp).Offset(1, -2).PasteSpecial
    End If
If cell.Value = "Special Agent" Then
cell.EntireRow.Copy
Sheets("Special Agent").Range("C" & Rows.Count).End(xlUp).Offset(1, -2).PasteSpecial
    End If
If cell.Value = "Super Agent" Then
cell.EntireRow.Copy
Sheets("Super Agent").Range("C" & Rows.Count).End(xlUp).Offset(1, -2).PasteSpecial
    End If
        Next
        
For Each cell In mrStatus

If cell.Value = "Closed" Then
cell.EntireRow.Copy
Sheets("Closed Production").Range("C" & Rows.Count).End(xlUp).Offset(1, -2).PasteSpecial
    End If
        Next
        
Application.CutCopyMode = False

End Sub

Is this what you were looking for?

Best regards,
Trowa
0
LP
Feb 21, 2013 at 03:37 PM
Trowa, I found your bit of code and it fits well in my spreadsheet. I have 2 issues though that I cannot seem to find an answer to.
1) I only want to move PART of the row (specifically, "A:Q") to the other worksheet, not the entire row
2) Every time I run the macro, I want it to overwrite the information previously sent to that worksheet. Right now, I run the macro and get, say, 10 results sent to the other worksheet. When I run it again, I get the same 10 results all duplicated on that worksheet.

How can I modify the code above to fix these two issues
0

Similar discussions

How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses
Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response