Filter and Copy Macro

[Elyssa Kurtz]

Hi,

I am trying to copy rows from Sheet1 to Sheets2-6 that fits a certain criteria. There will need to be 5 different macros, one for each sales person.

Here's my data:
Row 1 = Lot Number---Contract Date---Sales Person
Row 2 = 1---------10/05/2007---------EAK
Row 3 = 2---------01/10/2006----------CH
Row 4 = 3---------03/27/2007----------MM
Row 5 = 4---------11/11/2006----------EAK

I would like to run a macro that would take rows 2 and 5 from Sheet1 and paste them onto Sheet2 based on the sales person. And then another macro for the next sales person and so on.

 

[Norie]

Elyssa

What makes you think you need 5 macros?

Also what determines which sheet which sales person's data goes to?

Here's one example of how to split out data using criteria.

Sub DistributeRows()
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim LastRow As Long
Dim LastRowCrit As Long
Dim I As Long
    
    Set wsAll = Worksheets("All") ' change All to the name of the worksheet the existing data is on
    
    LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
    
    Set wsCrit = Worksheets.Add
    
    ' column C has the criteria eg sales person
    wsAll.Range("C1:C" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
    
    LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row
    For I = 2 To LastRowCrit
    
        Set wsNew = Worksheets.Add
        wsNew.Name = wsCrit.Range("A2")
        wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), _
         CopyToRange:=wsNew.Range("A1"), Unique:=False
        wsCrit.Rows(2).Delete
        
    Next I
    
    Application.DisplayAlerts = False
    wsCrit.Delete
    Application.DisplayAlerts = True
    
End Sub

 

[Elyssa Kurtz]

Hi Norie,

The reason I would like to create 5 macros is because the end user is not computer savvy. This way she can open the file, have one sheet with 5 macro boxes, she can click on each box separately and it will do the work for her.

There's no particular order to which sheet will get the data. But I'll just make Sheet2=EAK, Sheet3=CH, Sheet4=MM, etc.

 

[Norie]

Elyssa

Even if that's the case I still don't actually see the need for 5 different macros which essentially doing the same thing but with different criteria.