Autofiltering data across sheets

[basicallyawesome]

Let's say I have a workbook, Sheet 1 is called "Birds" and has everything you could ever want to know about birds. Column E is where I enter the type of bird in non-scientific terms, like Crow, Blackbird, Raven, Magpie, etc. These values are recurring so I could have several records for crows, and the other birds. I enter the data using a userform. When I am done I want to be able to hit a command button to kibosh the userform AND automatically filter the records into their own sheets within the workbook, named after each bird. Is this possible? How can I make this happen? Thank you!!

 

[Yongle]

So what do you want to end up with?
- a workbook comprising the original worksheet plus s worksheet for every unique value that appears in column E, containing the same columns as the original worksheet

 

[basicallyawesome]

So what do you want to end up with?
- a workbook comprising the original worksheet plus s worksheet for every unique value that appears in column E, containing the same columns as the original worksheet

Exactly.

 

[Yongle]

Try this
- amend Data to match your actual sheet name

In standard module

Sub BirdSheets()
    Application.ScreenUpdating = False
    Dim main As Worksheet, ws As Worksheet, sky As Range, bird As Variant, flight As Range
    Dim menagerie As New Collection
    Set main = Sheets("[COLOR=#ff0000]Data[/COLOR]")
    Set sky = main.Range("A1").CurrentRegion
    Set flight = main.Range("E2").Resize(sky.Rows.Count - 1)

    For Each bird In flight
        On Error Resume Next
        menagerie.Add CStr(bird), CStr(bird)
    Next bird
    
    For Each bird In menagerie
        DeleteSheet (bird)
        main.ShowAllData
        sky.AutoFilter Field:=5, Criteria1:=bird
        Set ws = Sheets.Add(After:=ActiveSheet)
        ws.Name = bird
        sky.Copy ws.Range("A1")
    Next bird
    Application.ScreenUpdating = True
End Sub

Private Sub DeleteSheet(SheetName As String)
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets(SheetName).Delete
    Application.DisplayAlerts = True
End Sub

To close the userform and run the code...
In Userform code

Private Sub [COLOR=#ff0000]CommandButton1[/COLOR]_Click()
    Unload Me
    Call BirdSheets
End Sub

 

[basicallyawesome]

Sorry for the delay, I finally had a chance to implement it; it worked and did exactly what I needed. Thank you so much!!

 

[Yongle]

Thanks for your feedback

 

[vcoolio]

@ Yongle:-

I like the variable names that you have used. Very clever!

I'm sure that the OP appreciates it also.

Cheerio,
vcoolio.

 

[Yongle]

@vcoolio - thank you