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?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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,
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top