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

palmer41420

New Member
Joined
Aug 16, 2012
Messages
14
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?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,496
Office Version
365
Platform
Windows
Hiya
Is this what your after?
Code:
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

New Member
Joined
Aug 16, 2012
Messages
14
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

Banned user
Joined
May 24, 2014
Messages
1,336
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

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,095,231
Messages
5,443,262
Members
405,220
Latest member
gtgaabaron

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top