segregating data in an excel sheet


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

Vishesh's picture


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
        wks.Range("A1").PasteSpecial xlPasteAll
    Next rngCell
    Set rngUnique = Nothing
    Set rngData = Nothing
    Set rngCell = Nothing
    Set wks = Nothing
End Sub



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.