Copy and past whole rows to a different tab based on two criteria

[jaymichaeljones]

Good Afternoon,

I'm new here and would appreciate any help that can be given. I've got a sheet that is attached here whereby I would like to move the whole row to another tab based on a set criteria.

Is there a generic VBA that i can plug my data into in order to make this happen? If you look at the below data set, I want to have anything labeled "REP 1" and "Won" moved over to a tab that is called "REP 1". I have 5 seperate reps, each with their own tab, that i'd be moving data to from the raw data set as displayed below:

Below is the data set I'm working from that the data would be copied FROM.

So the two criteria would be the REP #, and won. I'd like any sale won by each rep to be moved to that reps tab.

I hope this makes sense, and again I'd be truly thankful for any help that could be given here.

Best,

J


[TABLE="width: 500"]
<tbody>[TR]
[TD]Lead Name[/TD]
[TD]Size[/TD]
[TD]Assigned To[/TD]
[TD]Archived at[/TD]
[TD]Pipeline[/TD]
[TD]Archived By[/TD]
[TD]Won/Lost[/TD]
[TD]Cause[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIKE[/TD]
[TD]5[/TD]
[TD]REP 1 [/TD]
[TD]5:50 PM[/TD]
[TD]1st[/TD]
[TD]DAVID[/TD]
[TD]WON[/TD]
[TD]PRICE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]8[/TD]
[TD]REP 3 [/TD]
[TD]6:00 PM[/TD]
[TD]2nd[/TD]
[TD]Stacy[/TD]
[TD]LOST[/TD]
[TD]Negotiating With Someone else[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming ..
- All the 'REP' sheets already exist
- The main data sheet (the one you have shown sample data for) always has something in column A
- Column G is the column with WON/LOST etc and that value is entered manually
- Once copied to the 'REP' sheet you want the row(s) deleted from the main sheet

.. then, with the main sheet as the active sheet

1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by changing 1 or multiple values in col G.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Test in a copy of your workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range, rDel As Range
  Dim r As Long

  Set Changed = Intersect(Target, Columns("G"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    Set rDel = Range("A1")
    For Each c In Changed
      If UCase(c.Value) = "WON" Then
        r = c.Row
        Rows(r).Copy Destination:=Sheets(Cells(r, "C").Value).Cells(Rows.Count, "A").End(xlUp).Offset(1)
        Set rDel = Union(rDel, c)
      End If
    Next c
    If rDel.Cells.Count > 1 Then Intersect(rDel, Changed).EntireRow.Delete
    Application.EnableEvents = True
  End If
End Sub

 

[jaymichaeljones]

Thanks for the warm response and welcome! I did all that was outline, and it did not move the rows from the Main sheet to corrosponding rep sheets. Was it suppose to happen automatically?

 

[jaymichaeljones]

I just realized. Also, i Do not want the data to get deleted after pasting. I wish the data to be copied and pasted, not cut and pasted. Sorry for the lack of clarity on that.

 

[Fluff]

Cross posted http://www.vbaexpress.com/forum/sho...rows-to-a-different-tab-based-on-two-criteria

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Peter_SSs]

Also, i Do not want the data to get deleted after pasting. I wish the data to be copied and pasted, not cut and pasted.

Dealing with that first, the code would be altered to ..

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range

  Set Changed = Intersect(Target, Columns("G"))
  If Not Changed Is Nothing Then
    For Each c In Changed
      If UCase(c.Value) = "WON" Then
        Intersect(c.EntireRow, ActiveSheet.UsedRange).Copy Destination:=Sheets(Cells(c.Row, "C").Value).Cells(Rows.Count, "A").End(xlUp).Offset(1)
      End If
    Next c
  End If
End Sub

I did all that was outline, and it did not move the rows from the Main sheet to corrosponding rep sheets. Was it suppose to happen automatically?

It should happen automatically whenever "Won" is entered into column G. I am assuming that column G is entered manually. If column G is a formula, what is the formula?

If the data is already in the sheet, then select column G and Copy/Paste over itself and that should trigger the code. Post back if still not working & we'll check some other things.

 

[jaymichaeljones]

That seems to have done the trick! It looks, however, like it's now recorded each "won" item Four times per item. So in other words, on each respective tab, I am seeing four sets of each item that has been designated as 'won". - Thank you very much thus far! I'ts great to see it work! How, now, would I make it so that it only displays each "won" item once per instance. I only wish to see one set of "won" items in each respective sheet.

 

[Peter_SSs]

I am seeing four sets of each item that has been designated as 'won". - ... How, now, would I make it so that it only displays each "won" item once per instance. I only wish to see one set of "won" items in each respective sheet.

That sounds to me like either ..
- The code was working before when you thought that it wasn't, or
- You have now Copy/Pasted over the data 4 times

Since you are working on a copy of your workbook (I hope), I suggest that you delete all the data (except any headings) from all of the REP sheets and then Copy/Past column G once in the main sheet and then check the REP sheets again.

Then after that, individual rows should get copied when "Won" is entered into column G.