Home » Forums » Open Source Learning Center » Question and Answer

segregating data in an excel sheet

Submitted by lakshmiknagesh on 6 August, 2011 - 19:31

hi,

Can anybody help with this requirement. I have an Excel sheet with column A containg Bank Branch name and column B containing some numbers.
For eg: A B
branch1 10
branch1 20
branch2 11
branch2 11

I want a macro which copies branch1 details in one sheet, branch2 details in another sheet etc

‹ I need the formula that gives the nunber of same data Select specific button on internet explorer window,by pressing CTRL+M(macro) ›
»
Vishesh's picture

Segregate

Submitted by Vishesh on 6 August, 2011 - 20:34.
Put this code in a general module and change the parameters as asked in the code comments. 
Sub Segregate()
    Dim rngUnique As Range
    Dim rngData As Range
    Dim rngCell As Range
    Dim wks As Worksheet
 
    '----------------------------------------------
    'Change these parameters according to your requirement
    'Set the data range
    Set rngData = Sheet1.Range("A1").CurrentRegion
    'Write unique branch names in cells
    Set rngUnique = Sheet1.Range("E2:E4")
    '==============================================
    For Each rngCell In rngUnique
        rngData.AutoFilter Field:=1, Criteria1:=rngCell.Value
        Set wks = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        wks.Name = rngCell.Value
        rngData.SpecialCells(xlCellTypeVisible).Copy
        wks.Range("A1").PasteSpecial xlPasteAll
        rngData.AutoFilter
    Next rngCell
 
    rngData.Parent.Activate
 
    Set rngUnique = Nothing
    Set rngData = Nothing
    Set rngCell = Nothing
    Set wks = Nothing
End Sub
»

segregate

Submitted by Anonymous on 17 April, 2012 - 12:15.

hi,

The Branch names are not unique. I want all the records for a particular branch to come in one sheet preferably with sheet name as branch name.

»