Find name in column and copy to relevant worksheet

[thedeadzeds]

Hi Guys,

I have a list of data with a persons name in column N. For each person i have created a tab which is identical to their name in column N. Is there a way to filter by that person and copy all of the data to the relevant named tab as values. At the moment i am doing the below but there must be an easier way? Many thanks

Sub Copy_to_all_staff_sheets()

    'Bethan
    Sheets("Bethan").Select
    Cells.Select
    Selection.ClearContents
    Sheets("Blue Allocated").Select
    ActiveSheet.ShowAllData
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A:$N").AutoFilter Field:=14, Criteria1:= _
        "Bethan"
    Columns("A:N").Select
    Selection.Copy
    Sheets("Bethan").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
    'Hannah
    Sheets("Hannah").Select
    Cells.Select
    Selection.ClearContents
    Sheets("Blue Allocated").Select
    ActiveSheet.ShowAllData
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A:$N").AutoFilter Field:=14, Criteria1:= _
        "Hannah"
    Columns("A:N").Select
    Selection.Copy
    Sheets("Hannah").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[Kamolga]

Hi,
You could indeed loop through name

For each cell in Worksheets("[COLOR=#0000ff]Data[/COLOR]").Range("[COLOR=#0000cd]N2:N15[/COLOR]")
Sheets(cell.value).Select
    ActiveSheet.Cells.ClearContents
    Sheets("Blue Allocated").Select
    ActiveSheet.ShowAllData
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A:$N").AutoFilter Field:=14, Criteria1:= cell.value
    Columns("A:N").Select
    Selection.Copy
    Sheets(cell.value).Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Next cell

 

[Fluff]

How about the same way as I showed you here https://www.mrexcel.com/forum/excel...a-codes-together-post5180005.html#post5180005

 

[taigovinda]

Hi,

If you know for sure that all the names will have results (and that you have a sheet for every name you want to copy out), you could use this adaptation of your code below.

I've seen examples in the past on this site where code will look at your data and generate all the individual sheets - so you would start with only your one data sheet and not need to know which names are there. That code was probably much faster than what I'm providing below too... but if this is all you need then you could use it!

Edit: here is an example from one of the better Excel tutorial websites: https://contexturesblog.com/archives/2012/02/21/filter-excel-data-onto-multiple-sheets/
Edit2: a lot of answers between when I started and when I posted! ...the code in post #2 is the simplest but will run once per row; depending on the data set that might be fine but it could be doing an awful lot of unnecessary looping.

Option Explicit


Sub Copy_to_all_staff_sheets()
'assume sheet1 has the data and the individual folks' sheets are in position 2,3
Dim i As Integer


Application.ScreenUpdating = False 'speeds up execution
Application.Calculation = xlCalculationManual 'speeds up execution


For i = 2 To 3
    'Bethan
    Sheets(i).Cells.ClearContents
    With Sheets("Blue Allocated")
        .AutoFilterMode = False
        .Range("$A:$N").AutoFilter Field:=14, Criteria1:=Sheets(i).Name
        .Columns("A:N").Copy
        Sheets(i).Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
Next


Sheets("Blue Allocated").ShowAllData


Cleanup:
Application.ScreenUpdating = True 'revert to proper setting
Application.Calculation = xlCalculationAutomatic


End Sub

Tai

 

[Kamolga]

data set that might be fine but it could be doing an awful lot of unnecessary looping.

That depends on N2:n15. Can define last row dynamically

Since you ask for an easier way, I would put name in a table list, that is dynamic and easy to manage

That is not easier but I would personnally avoid those sheets filtering copy pasting by
Option1 : having the data in BlueAllocated (also in a table) spread in VBA Arrays (it is like creating a sheet in VBA) so I can print values. To me it is cleaner, more dynamic and quicker operations.
Option2 : one sheet with a drop down list (ActivX) of the employees and specific values appear on change, so the importation of the data is made for one person only when I change this value

 

[taigovinda]

Hi, your code loops 14 times and does the same operation 14 times, even if the names are all the same name in N2:N15. That was the basis of my comment; it is brute force and inefficient, but the end result will be right and it's very simple. I'm sure you could write something better if you wanted. I did - and Fluff already did even moreso in a different thread... and Debra did even moreso than that many years ago in the post I linked.

Cheers!

That depends on N2:n15. Can define last row dynamically

Since you ask for an easier way, I would put name in a table list, that is dynamic and easy to manage

That is not easier but I would personnally avoid those sheets filtering copy pasting by
Option1 : having the data in BlueAllocated (also in a table) spread in VBA Arrays (it is like creating a sheet in VBA) so I can print values. To me it is cleaner, more dynamic and quicker operations.
Option2 : one sheet with a drop down list (ActivX) of the employees and specific values appear on change, so the importation of the data is made for one person only when I change this value

 

[Kamolga]

your code loops 14 times and does the same operation 14 times, even if the names are all the same name in N2:N15

It is in different color,to be adapted (as the name of the sheet "Data" which does not even exist), this is supposed to represent the fix list of employees (no duplicate) somewhere on a sheet in a column N .