Split groups into new worksheets in one workbook

Posted on 2011-05-04
Last Modified: 2012-05-11
I have a workbook that has the a grouping column "group" it has a value of either 5, 6, 7, 12, 13 ,14. (see attached)

I would like a way to separate the groups into their own worksheets within the workbook.

Macro? Formula? Annotation is always appreciated.

Question by:justearth
    LVL 39

    Accepted Solution

    Hi JE,

    The attached macro should do just that.

    Sub SplitListIntoWorksheets()
    'split list into individual worksheets
    Dim lastROw As Long, i As Long
    Dim shtData As Worksheet, lgCol As Long, rgSel As range
    Dim cUnique As New Collection, shtDest As Worksheet
    Const blTitles As Boolean = True                    'true if the data has titles, false otherwise
    Const sColumn As String = "A"                       'Which column should the list be split on
    application.ScreenUpdating = False
    application.Calculation = xlCalculationManual
    application.DisplayAlerts = False
    lgCol = Cells(1, sColumn).Column
    Set rgSel = Cells(1, 1).CurrentRegion
    lastROw = Cells(Rows.Count, lgCol).End(xlUp).Row 'get last row
    Set shtData = ActiveSheet
    'load the column contents in a collection, to keep individual values
    On Error Resume Next
    For i = 2 To lastROw
        If Cells(i, lgCol) <> Cells(i - 1, lgCol) Then
            cUnique.Add Cells(i, lgCol), CStr(Cells(i, lgCol))
        End If
    On Error GoTo 0
    'for each individual value, filter the list, copy the results to a new workbook, save and close the new workbook
    For i = 1 To cUnique.Count
        shtData.AutoFilterMode = False
        rgSel.CurrentRegion.AutoFilter Field:=lgCol - rgSel.CurrentRegion.Column + 1, Criteria1:=cUnique(i)
        Set shtDest = Sheets.Add
        shtDest.Name = "Data " & cUnique(i)
        rgSel.CurrentRegion.Copy shtDest.Cells(1, 1)
    shtData.AutoFilterMode = False
    application.ScreenUpdating = True 'reenable ScreenUpdating
    application.Calculation = xlCalculationAutomatic
    application.DisplayAlerts = True
    End Sub

    Open in new window


    Author Comment


    Author Closing Comment

    LVL 39

    Expert Comment

    Glad to help

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Join & Write a Comment

    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now