Create New Worksheet Based On Values within a Cell, then Insert Row

[Ocean Zhang]

I hope this is something that can be done in Excel:

This has 2 parts:

1. In column O, I have comma-separated values that represent different product categories. For each unique comma-separated value, I want to create a new worksheet within the current workbook with the same sheet name as the comma-separated value.

2. Insert each row into the corresponding new sheets.

See example sheet attached.

1. From column O, there are 10 comma-separated values, 8 of which are unique, so these 8 new sheets would be created: diaper bags, feeding, shopping, decor, rockers, strollers, play, bath.
2. Row 1 would be inserted into the following worksheets: diaper bags, feeding, and shopping. Row 2 would be inserted into decor and feeding. Row 3 would be inserted into rockers, strollers, play, bath, diaper bags.

If it's not possible to insert the entire row, then inserting just values in column A would be good enough.

Thanks!

[Ocean Zhang]

I forgot to include the attachment with my original post. Edited to add attachment.

[jindon]

See if this is how you wanted.

[Ocean Zhang]

Wow, You're my HERO! Awesome stuff. Thanks so much!

[Ocean Zhang]

Wait, there was a problem somewhere. For some reason it worked on the example worksheet, but it didn't work properly on my actual production sheet.

I've uploaded the actual file I'll be working on.

Thanks so much for your help!

[jindon]

1) Replace "Sheet1" with "Complete Vendor List"

2) You have invalid character for the sheet name.
e.g
: \ ? [ ] / *

You need to delete all these from the categories.
And the Length of the sheet name MUST be less than 31 characters.

[Ocean Zhang]

Okay, I cleaned it up and tried again. This time I get a run time error

I attached my current version without special characters and less than 31 characters long. Can someone please investigate?

Thanks!

[jindon]

Can you change

    For Each e In dic
        If Not IsSheetExists(e) Then
            Sheets.Add(after:=Sheets("Complete Vendor List")).Name = e
        End If
        With Sheets(e)
            .Cells.Clear
            dic(e).Copy .Cells(1)
            With .Cells(1).CurrentRegion
                .Columns(15).Offset(1).Resize(.Rows.Count - 1).Value = e
                .Columns.AutoFit
            End With
        End With
    Next

to

    For Each e In dic
        If e <> "" Then
            If Not IsSheetExists(e) Then
                Sheets.Add(after:=Sheets("Complete Vendor List")).Name = e
            End If
            With Sheets(e)
                .Cells.Clear
                dic(e).Copy .Cells(1)
                With .Cells(1).CurrentRegion
                    .Columns(15).Offset(1).Resize(.Rows.Count - 1).Value = e
                    .Columns.AutoFit
                End With
            End With
        End If
    Next

[Ocean Zhang]

Yes, it worked! Thanks!

[arlu1201]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.