PDA

View Full Version : Create sheets using data shown in filter



Klartigue
01-08-2015, 10:48 AM
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
01-09-2015, 08:52 AM
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
01-13-2015, 06:53 AM
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
01-13-2015, 09:03 AM
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
03-10-2015, 08:22 AM
Thank you Mr. VBAX Master.