Is there a way to automatically Filter, Copy and Paste to Tabs based on value in ColumnA

[palmer41420]

Is there any way to automatically filter, copy, and paste from a large data set into tabs within the same file?

I work in a school system with over 50 schools. I frequently gather data in a large spreadsheet containing all schools. Each school has a unique identifier in Column A. Once I complete the large data set, I then have to create a tab for each school, filter in Column A to view only results for the matching school, copy and paste to the new tab created.

There has to be a better way than doing this 50+ times every time I get a new project. My pipe dream is that I would name the tabs with the same name as the unique identifiers in Column A for each school then have a script that does the filter, copy, and paste for me. I attempted to create a macro but it seemed to get hung up when I used Ctrl+Shift+DownArrow to highlight all rows before copying (there aren't a consistent amount of rows for each school).

Bonus: Is there a way to go one step further and autocreate the tabs as well?

Does anyone have any suggestions?

 

[Fluff]

Hiya
Is this what your after?

Sub Palm()

    Dim Skl As Variant
    Dim ValU As Variant
    Dim Schls As Variant
    Dim Dict As Scripting.Dictionary
    Dim Lcnt As Long

    Worksheets("[COLOR=#ff0000]Schools[/COLOR]").Activate
    Skl = Range("A2", Range("A" & Rows.Count).End(xlUp))
    Set Dict = CreateObject("scripting.dictionary")

    With Dict
        .CompareMode = vbTextCompare
        For Each ValU In Skl
            If Not IsEmpty(ValU) Then
                If Not .Exists(ValU) Then .Add ValU, Nothing
            End If
        Next
        Schls = .Keys
    End With

    For Lcnt = 0 To Dict.Count - 1
        Sheets.Add.Name = Schls(Lcnt)
        With Worksheets("[COLOR=#ff0000]Schools[/COLOR]")
            .Range("A1").AutoFilter field:=1, Criteria1:=Schls(Lcnt)
            .Cells.SpecialCells(xlCellTypeVisible).Copy Sheets(Schls(Lcnt)).Range("A1")
        End With
    Next Lcnt

End Sub

You'll need to change the sheetname in red to match your file & you'll also need to add a reference.
In the VB Editor select Tools > References > Microsoft Scripting Runtime ensuring that the checkbox is ticked

 

[palmer41420]

That is exactly what I wanted! However, I have too much data for it process. It finishes about 5 tabs of the 51 then freezes and says it cant handle that much data. I will definitely use this on smaller scale projects though.

 

[DocAElstein]

Is there any way to automatically filter, copy, and paste from a large data set into tabs within the same file? ....... My pipe dream is that I would name the tabs with the same name as the unique identifiers in Column A for each school then have a script that does the filter, copy, and paste for me. ...........
Bonus: Is there a way to go one step further and autocreate the tabs as well?

Does anyone have any suggestions?

Hi,
. I think I may have a solution for you. I did something for I think about 20 tabs. Can’t remember exactly how big the file was (or where it is!) , but I will try to dig it out!
. As a beginner I do not like to supply codes until I have tested them on data at least similar to that required!!
. Could you therefore in the meantime supply an example of that sheet you have which has many schools referenced by a unique identifier in column A. You could shorten it a bit and change or make up some of the data in case any real info is sensitive or private etc.
. I expect a screen shot is unpractical because of the size of the file.

. Maybe you could send an excel file over any file share thing, For example send over this free thing: Box Net,
Remember to select Share after uploading and give us the link they provide.

Alan.

 

[Smitty]

You should be able to do what you want with a Pivot Table.

First format the data as a Table (which will make the range dynamic), then create a pivot table off of it (you can duplicate the sheet in a pivot table by putting all fields into the Row field).

Format the pt to suit, then put the column A/School Name field into the Pivot Table Filter field. From there goto Pivot Table Options-->Show Report Filter Pages and Excel will create a new pivot table worksheet for you for each school in the filter field.

My pipe dream is that I would name the tabs with the same name as the unique identifiers in Column A

This method will do that automatically.

The great thing about this method is that you only need to do it once. If you add new data to the table, then just goto Refresh All and all of the Pivot Tables will update with the new data.

HTH,