use VBA to auto-filter and copy filtered data to another sheet

[Eliehaddad]

Hello,

please i need you assistance to automaticaly filter data in a specific table based on the customer name and copy the filtered data related to this customer to another worksheet using VBA

example: i need to automaticaly filter the projects by the name of the project owner and autocopy the related data of the projects to another sheet.

[TABLE="width: 500"]
<tbody>[TR]
[TD]project name[/TD]
[TD]decsription[/TD]
[TD]project owner[/TD]
[/TR]
[TR]
[TD]mars[/TD]
[TD]XXX[/TD]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]venus[/TD]
[TD]YYY[/TD]
[TD]Mark[/TD]
[/TR]
[TR]
[TD]earth[/TD]
[TD]ZZZ[/TD]
[TD]Sam[/TD]
[/TR]
</tbody>[/TABLE]

so will have this result in the 2nd sheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]project name[/TD]
[TD]decsription[/TD]
[TD]project owner[/TD]
[/TR]
[TR]
[TD]mars[/TD]
[TD]XXX[/TD]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]earth[/TD]
[TD]ZZZ[/TD]
[TD]Sam[/TD]
[/TR]
</tbody>[/TABLE]


thank you in advance

 

[Fluff]

Ok, try this

Sub MoveDupes()
   Dim ws As Worksheet
   Dim Cl As Range
   Dim sht As Worksheet
   
   For Each sht In Sheets(Array([COLOR=#ff0000]"Joseph", "Nihale", "Lea"[/COLOR]))
      sht.Range("A5:A" & Rows.Count).EntireRow.Clear
   Next sht
   Set ws = Sheets("Report")
   If ws.AutoFilterMode Then ws.AutoFilterMode = False
   With CreateObject("scripting.dictionary")
      For Each Cl In ws.Range("C5", ws.Range("C" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) And Cl.Value <> 0 Then
            .Add Cl.Value, Nothing
            ws.Range("A4").AutoFilter 3, Cl.Value
            ws.AutoFilter.Range.Offset(1).Copy Sheets(Split(Cl.Value)(0)).Range("A" & Rows.Count).End(xlUp).Offset(1)
         End If
      Next Cl
   End With
   ws.AutoFilterMode = False
End Sub

If you have more Project Owners add them to the array in red

 

[Eliehaddad]

Thaaaaaannnnnnkkkkk yooouuuuuu
i really appreciate it

 

[Fluff]

Glad we could help & thanks for the feedback