Splitting up data into separate sheets based on a column

Hi all,

Decent with excel but horrible with macros. I've attached a demo of what I would like to do. I want to take column c as my identifier and use it to create worksheets with the same name and all the records that are associated in each matching row as well. In the example, I have "summary" as my before product and "sales/marketing/graphic design" sheets as the potential finished product. Any easy way to do this?

Thank you.

No dice on that, the error message states:

Compile error:
Member already exists in an object module from which this object module derives.

The first line, sub move() , and option explicit is only listed once.

Yours looks like its working because you already have sheets called "Marketing/Graphic Design/Etc..". When I run this every quarter, some of these fields will change. Is there a way to modify the macro to create sheets with unique identifiers as found in column C

Use AutoFilter...

Sub test()
    Dim e
    Application.ScreenUpdating = False
    With Sheets("summary").Cells(1).CurrentRegion
        .Parent.AutoFilterMode = False
        For Each e In Filter(.Parent.Evaluate("transpose(if(countif(offset(" & _
            .Columns(3).Address & ",,,row(1:" & .Rows.Count & "))," & _
            .Columns(3).Address & ")=1," & .Columns(3).Address & ",char(2)))"), Chr(2), 0)
            If e <> .Cells(3).Value Then
                If Not IsSheetExists(e) Then Sheets.Add(after:=Sheets(Sheets.Count)).Name = e
                Sheets(e).Cells.Clear
                .AutoFilter 3, e
                .Copy Sheets(e).Cells(1)
                .AutoFilter
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Function IsSheetExists(ByVal txt As String) As Boolean
    On Error Resume Next
    IsSheetExists = Len(Sheets(txt).Name)
    On Error GoTo 0
End Function

this works perfectly on my test spreadsheet, but when i move it over to my actual data that has 41 columns (with the key indicator in column 40), where do I change the code to use column AO as the column of choice?

Try change 3s to the col reference that you want
1)

        For Each e In Filter(.Parent.Evaluate("transpose(if(countif(offset(" & _
            .Columns(3).Address & ",,,row(1:" & .Rows.Count & "))," & _
            .Columns(3).Address & ")=1," & .Columns(3).Address & ",char(2)))"), Chr(2), 0)

2)

       .AutoFilter 3, e

This is getting closer, it created the sheets based on column 40 like I wanted, but it didn't transfer any of the main data over. So I have a row of headers from the main data in A1:AO1 with nothing underneath in A2:A02

Then I need to see your actual data layouts.

Reattached the example. The column I would need to utilize is column 40 (department).

Working...

Sub test()
    Dim e
    Const myCol As Long = 9  '<- adjust the col ref.
    Application.ScreenUpdating = False
    With Sheets("summary").Cells(1).CurrentRegion
        .Parent.AutoFilterMode = False
        For Each e In Filter(.Parent.Evaluate("transpose(if(countif(offset(" & _
            .Columns(myCol).Address & ",,,row(1:" & .Rows.Count & "))," & _
            .Columns(myCol).Address & ")=1," & .Columns(myCol).Address & ",char(2)))"), Chr(2), 0)
            If e <> .Cells(myCol).Value Then
                If Not IsSheetExists(e) Then Sheets.Add(after:=Sheets(Sheets.Count)).Name = e
                Sheets(e).Cells.Clear
                .AutoFilter myCol, e
                .Copy Sheets(e).Cells(1)
                .AutoFilter
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Function IsSheetExists(ByVal txt As String) As Boolean
    On Error Resume Next
    IsSheetExists = Len(Sheets(txt).Name)
    On Error GoTo 0
End Function

This works, thank you!