Create sheets using data shown in filter

On the attached sheet, I would like to create a tab for every broker seen when you filter columnA (and name it each respective broker name). So on the attached, after Sheet1, it is possible to use a vba code to read what is filtered in A (in this case JP, AAM, CITI) and rename three tabs after each of the broker? I went ahead and renamed the tabs on the attached so you could see the desired results. Is this possible using a VBA code?

Looking at your questions, I have a suggestion.

I think that using the built in Excel pivot tables and pivot charts capabilities would provide what you've asked about and more, as well as possibly providing more flexibility to you without having to use any VBA

I used your sample data and created some Sell lines also just for completeness

Whenever I use a PT in Excel, I separate collecting the data in a simple List (ws = 'Data') from the reporting and analysis ('ws = 'PivotTable- .....')

Attached is a VERY simple example of the flexibility that the Excel PT capability offers



Just a suggestion

Not sure where I got this - but it works for me . Run the code, and when it prompts with the window, select the Broker column. I either borrowed or bought this code, and it has proven very useful. Clean too...(cleaner than pivot)

Code:

---

Sub SplitOutSheets1()
    Dim LastRow As Long
    Dim iStart  As Long
    Dim iEnd    As Long
    Dim i      As Long
    Dim LastCol As Long
    Dim iCol    As Integer
    Dim ws      As Worksheet
    Dim r      As Range
    On Error Resume Next
    Set r = Application.InputBox("Click in the column to extract by", Type:=8)
    On Error GoTo 0
    If r Is Nothing Then Exit Sub
    iCol = r.Column
    Application.ScreenUpdating = False
    With ActiveSheet
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        .Range(.Cells(2, 1), Cells(LastRow, LastCol)).Sort _
            Key1:=Cells(2, iCol), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        iStart = 2
        For i = 2 To LastRow
            If .Cells(i, iCol).Value <> .Cells(i + 1, iCol).Value Then
                iEnd = i
                Sheets.Add After:=Sheets(Sheets.Count)
                Set ws = ActiveSheet
                On Error Resume Next
                ws.Name = .Cells(iStart, iCol).Value
                On Error GoTo 0
                ws.Range(Cells(1, 1), Cells(1, LastCol)).Value _
                        = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
                With ws.Rows(1)
                    .HorizontalAlignment = xlCenter
                    With .Font
                        .ColorIndex = 5
                        .Bold = True
                    End With
                End With
                .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy _
                        Destination:=ws.Range("A2")
                iStart = iEnd + 1
            End If
        Next i
    End With
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
 End Sub

---

Geeta,

I formatted your code by enclosing it inside Code Tags. You can easily do it yourself by using the # Button on the Editor menu Bar.

You can click the button and paste or type your code in between the inserted code tags, or you can select any existing code and then click the button.

Thank you Mr. VBAX Master.