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]

Please note the board policy is that all communication is done in the thread, hence I removed your whatsapp number.

 

[Eliehaddad]

data will be filtered and copied from report sheet to Joseph or lea or nihale sheet so if i need to see all the projects i can see them in report sheet and if i need to see only joseph projects i can see them in joseph sheet

thank you so much

 

[Fluff]

Ok, try this

Sub MoveDupes()
   Dim ws As Worksheet
   Dim Cl As Range
   
   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) 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

But you will need to remove those extra formulas from below the data.

 

[Eliehaddad]

it is working in joseph sheet
but
ws.AutoFilter.Range.Offset(1).Copy Sheets(Split(Cl.Value)(0)).Range("A" & Rows.Count).End(xlUp).Offset(1) is giving me an error : subscript out of range
and one more thing what should i change to apply this VBA to the other sheets like (nihale and Lea sheets)

thanks

 

[Fluff]

With the file you supplied it copied to both the Joseph sheet & the lea sheet.
I suspect the error you got was because of all the extra formulae below the data.

 

[mumps]

Using Fluff"s suggested code, I think that this should take care of the extra formulae below:

Sub MoveDupes()
   Dim ws As Worksheet
   Dim Cl As Range
   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 <> 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

 

[Eliehaddad]

i removed all the data and applied it
i't's works for on time only and when i try to change the project owner form the project sheet "going to mars" for example it will not update the other sheet even if i rerun macro, i will send you back the sheet to know what i mean
https://drive.google.com/file/d/1FBDVQPUK0kYHCtEQXbeArJ3WdW6-kJ-k/view?usp=sharing
when u test it u will see
it seems this problem cannot be solved

 

[mumps]

The macro simply copies the data from the Report sheet to the appropriate Project Owner sheets. When you change the project owner in cell B7 in the project sheet "going to mars", what sheet do you want to update? Please be very detailed in your explanation referring to specific cells and worksheets.

 

[Fluff]

When you re-run the macro it adds the data to the bottom of the sheets.
Do you want the existing data on the Names sheets to be removed when re-running the macro?

 

[Eliehaddad]

sure
i have many projects sheets, let's only work on 3 of them in this example, one is called "Mars" the 2nd "venus" and the 3rd "pluto"

- They are all related to a sheet called "Report" where the data in the 3 sheets are all copied to the "Report sheet"

- I created 3 other sheets each one in a name of a project owner where only project made by each owner will be copied in this sheet.

for example only project made by joseph will be copied to "Joseph Sheet"
and so on...
- sure i do not want duplicated data, so when a project owner name is changed "B7" from Joseph to Nihale in a specific project sheet "Pluto for example" the data of this project must automaticaly be removed fro "Joseph " sheet and be added to "Nihale" Sheet

hope this description will help and if you use the sheet you will directly understand my idea

Thankssssss