PDA

View Full Version : Copy and past whole rows to a different tab based on two criteria



JayMichaeljo
03-24-2018, 01:07 PM
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




Lead Name
Size
Assigned To
Archived at
Pipeline
Archived By
Won/Lost
Cause



MIKE
5
REP 1
5:50 PM
1st
DAVID
WON
PRICE



David
8
REP 3
6:00 PM
2nd
Stacy
LOST
Negotiating With Someone else

mana
03-24-2018, 07:18 PM
Option Explicit


Sub test()
Dim r As Range
Dim c As Range

Set r = Cells(1).CurrentRegion
Set c = r(1).Offset(, r.Columns.Count + 1)
r.Columns(3).AdvancedFilter xlFilterCopy, , c, True
c.Offset(, 1).Value = r(7).Value
c.Offset(1, 1).Value = "WON"


Do While c.Offset(1).Value <> ""
r.AdvancedFilter xlFilterCopy, c.Resize(2, 2), Worksheets(c.Offset(1).Value).Cells(1)
c.Offset(1).Delete xlShiftUp
Loop
c.CurrentRegion.ClearContents

End Sub



マナ