Formula, macro or VBA to create new tabs based on column data

[r011ingthunder]

Hi all,
I frequently have to generate a spreadsheet (ex SAGE) similar to the attached. It changes each time.
What I want to have is a 'template' spreadsheet that when I paste the data into the first sheet, it opens a new tab for each unique value in Column C, and copies all the rows containing that unique Column C value to this new tab.
I'd also like it to rename the tab to the unique value from Column C. And I'd like it to copy the top row onto each tab as well. I've tried doing this with limited success using formulas, but it's not working out the way I want it to. Anybody know if what I want is even possible?
If you need any further info, please don't hesitate to ask.
FYI, I can work with formulas, but I'd have to be walked through macros or VBA.
Thanks

 

[mumps]

What do you mean by:

when I paste the data into the first sheet

Do you paste all the data in the sheet at once? Do you paste one cell at a time? Do you enter the data manually? Please explain in detail referring to specific cells, rows and columns using a few examples from your data.

 

[r011ingthunder]

Hi,
Yes, all data pasted into sheet in one go at the start.
No, I don't paste one cell at a time.
The sheet is generated ex SAGE, but I would copy and paste it into the new template if that works.
All of the data you see in the example is auto-generated from the SAGE package and exported to excel. I then do a little formatting (numbers, bold, freeze top row etc).
So the data is dumped in one go onto the sheet. I hope that makes sense.
Column D is the column with the unique data that I need to generate the extra tabs. I would like to populate each tab with that data.
For example, 1st unique value in Column D is WO0770. I would like it to create a new tab called WO0770, and copy all rows where WO0770 appears in column D (in my example just row 2).
Next is WO0868, of which there are three rows, and I would like those copied onto the next tab and it to be called WO0868, and so on.

 

[r011ingthunder]

Apologies it is COLUMN D that contains the unique data values, not column c as I mentioned in my first post.

 

[mumps]

After pasting the data, run this macro.

Sub CreateSheets()
    Application.ScreenUpdating = False
    Dim rng As Range, RngList As Object, srcWS As Worksheet
    Set srcWS = Sheets("Sheet1")
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each rng In Range("D2", Range("D" & Rows.Count).End(xlUp))
        If Not RngList.Exists(rng.Value) Then
            RngList.Add rng.Value, Nothing
            With srcWS.Cells(1).CurrentRegion
                .AutoFilter 4, rng
                If Not Evaluate("isref('" & rng.Value & "'!A1)") Then
                   Sheets.Add(After:=Sheets(Sheets.Count)).Name = rng
                   srcWS.AutoFilter.Range.Copy Cells(1, 1)
                End If
            End With
        End If
    Next rng
    srcWS.Range("D1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[r011ingthunder]

Hi mumps,
Ok, I've tried that.
Firstly, an error pops up (see attached). Once I click debug and close the macro window, the macro actually works. I've also attached a pic of the debug window.

 

[r011ingthunder]

Update:
There was a blank row in the middle of the data. Once I deleted the blank row, the macro worked perfectly!!!! Thank you mumps!!!!!

 

[mumps]

You are very welcome.