Create sheets using data shown in filter

[Klartigue]

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?

[Paul_Hossler]

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

[Geeta2013]

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)

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

[SamT]

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.

[Geeta2013]

Thank you Mr. VBAX Master.