Help with auto-copying rows from one table to another dependent on criteria

[Debs_575]

Hi there,
I'm new to this forum and new to VBA so apologies if I am not posting this in the right place or if nothing makes sense.
I'm trying to find some coding that will automatically copy data from one table to another depending on certain criteria. I have a table that lists a number of projects, the type of project and which team member has been allocated to which role (AD, Chair, TA, TL). I want each team member to have their own table in a separate worksheet that updates automatically once the data in the master sheet is edited. Team members can be allocated to one of the four roles per project so I want to be able to search for their name in the four role columns and if their name is present, copy the entire row to their own table in their own worksheet.
I have searched multiple forums trying to find coding that will fit what I want it to do but I only seem to be able to find a code that will look for text in one column not 4 like I want to do. If anyone is able to help it would be most appreciated.
I have tried to upload a simple example of what my tables may look like but it is saying there is a problem uploading the file. To give you an idea, column A is project no., B is Project type, C:F are the different roles for each project (AD, Chair, TA, TL).
I hope that makes sense.
Thanks
Debbie

 

[Luke M]

Hi Debs, and welcome to the forum.

I'm sorry to hear that the file isn't loading. First, what type of file format is it? Needs to be an xlsx or xlsm format. Second, there's a file size limit. It possible, try to reduce the size (amount of data) in the file. I don't recall what the exact limit it, but I'm guessing around 5 MB. If still having trouble, try creating a zip file, and you can then upload the zip folder to the forum.

Seeing an example of how your data is laid out is going to be fairly important to get a macro/formula that meets your criteria.

 

[Debs_575]

Thanks Luke. I've just tried again and it seems to be working now...strange! Please see the attached.
Thank you!

 

[Nebu]

Hi:

Try this code.I am hoping that you will not be having hundreds of thousands of rows

Thanks

 

[Debs_575]

Thank you for your help Nebu. It wasn't quite what I was looking for as I didn't want to have to use a button to refresh the individual sheets. I was hoping to get something that updated automatically. However, it still may be workable this way so thank you for helping me.

Most appreciated.

Kind regards

Debbie

 

[Nebu]

Hi:

You can use this code in any worksheet events to trigger the macro instead of a button.

Thanks

 

[BelindaJoy]

I'm looking to do something similar and am stuck. My company's security settings won't let me download an .xlsm file and look at the code so I can only make use of answers in the forum that post the code.
I do need something to act on the criteria to build the appropriate table only on demand, so with the click of a button, and to clear anything previously in the report table first if necessary. I've tried a few approaches none of which I've gotten to work reliably and robustly yet (filter then copy then clear filters, For loops cycling through the source table...) and am looking again for solutions others have found and this is one of the closest comparable examples I've found. Can anyone post some of the code on how it was resolved please?

 

[Nebu]

Hi:

Here is the code I have provided in the above file.

Sub SplitData()
Application.ScreenUpdating = False

Dim i As Long, j As Long, cnt As Long, k As Long
Dim rng As Range, found As Range
Dim sh As Worksheet

i = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
j = Sheet1.Cells(Rows.Count, "N").End(xlUp).Row

For Each sh In Worksheets
If sh.Name <> "Sheet1" Then
sh.Range("A2:F" & sh.Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
End If
Next
For k = 2 To i
    Set rng = Sheet1.Range("C" & k & ":F" & k)
    For cnt = 1 To j
        Set found = rng.Find(What:=Sheet1.Range("N" & cnt).Value, LookAt:=xlWhole)
        If Not found Is Nothing Then
            Sheet1.Range("A" & found.Row & ":F" & found.Row).Copy
            Sheets(Sheet1.Range("N" & cnt).Value).Range("A" & Sheets(Sheet1.Range("N" & cnt).Value).Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial
            Application.CutCopyMode = False
        End If
    Next
Next

Application.ScreenUpdating = True
End Sub

Thanks