Separating data to individual sheets

[riley454]

I have an extensive (5000+ rows) worksheet that has combined data for all 40 team members that I want to separate the data into a worksheet for each team member

A1-Date / B1-Team Member / C1-Data1 / D1-Data2 / E1-Data3 / F1-Data4

I'm hoping to run a macro that will copy A2:F2 and paste to the worksheet that is named the same as B2 then move onto copying A3:F3 and pasting to the worksheet that matches B3 etc

What are my options?

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim i As Long, v As Variant
    v = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(v, 1)
            If Not .Exists(v(i, 1)) Then
                .Add v(i, 1), Nothing
                With Range("A1")
                    .CurrentRegion.AutoFilter 2, v(i, 1)
                    ActiveSheet.AutoFilter.Range.Offset(1).Copy Sheets(v(i, 1)).Cells(Sheets(v(i, 1)).Rows.Count, "A").End(xlUp).Offset(1)
                End With
            End If
        Next i
    End With
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[riley454]

Thanks Mumps but I'm getting a 1004 runtime error.

Some of your code is new to me so I'll do some research later today and see if I can troubleshoot it

I'm using Office 2010 so not sure if that has any affect

 

[Joe4]

I'm using Office 2010 so not sure if that has any affect

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

All things equal, what you have is a relational database model, and as such, this is much easier to do with Relational Database programs, like Microsoft Access or SQL.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. I wouldn't need all 5000 rows, just enough rows that are representative of your data. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[riley454]

Thanks for the advice

I was having trouble getting my head around some of the code above due to my limited VBA knowledge but I came across this one from the "Listen Data" website (Excel VBA : Filtering and copy pasting to new sheet or workbook) which I can understand better and manipulate it to suit my needs

Sub filter()
Application.ScreenUpdating = False
Dim x As Range
Dim rng As Range
Dim last As Long
Dim sht As String

'specify sheet name in which the data is stored
sht = "DATA Sheet"

'change filter column in the following code
last = Sheets(sht).Cells(Rows.Count, "F").End(xlUp).Row
Set rng = Sheets(sht).Range("A1:F" & last)

Sheets(sht).Range("F1:F" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AA1"), Unique:=True

For Each x In Range([AA2], Cells(Rows.Count, "AA").End(xlUp))

With rng
.AutoFilter
.AutoFilter Field:=6, Criteria1:=x.Value
.SpecialCells(xlCellTypeVisible).Copy

Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value
ActiveSheet.Paste
End With
Next x

' Turn off filter
Sheets(sht).AutoFilterMode = False

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

End Sub

I'm not sure if it's a better or worse method than that from Mumps (I'll let others contemplate that) but it does what I needed with syntax that I am more familiar with.

Regardless I have included it so others with the same needs have another option to work with

Thanks again