I have a table where several records come from the A5 to the AH100

From these records I have to generate several new sheets, this would
be an example

Employee Type function
Carlos GT Wash.
Pedro JR Ironing
Pedro JR Fold
Raul KL Deliver
Raul KL Charge
Jose MT Shopping

So I need to be separated into various sheets for employee and full
row

Employee Type function
Carlos GT Wash.

Employee Type function
Pedro JR Ironing
Pedro JR Fold

Employee Type function
Raul KL Deliver
Raul KL Charge

Employee Type function
Jose MT Shopping

I have this code I found on the network and that if I generate new
leaf with the first employee but did not make me much more.

Sub Hoja_x_Auditor_Interno()
Application.ScreenUpdating = False
Dim Celda As Range
With Worksheets("Indicadores Internas").Range("A5").CurrentRegion
..Parent.Range("A6,AH6") = .Cells(1)
..Resize(, 1).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Parent.Range("BT6"), Unique:=True
..Parent.Range("BT6").Sort _
Key1:=.Parent.Range("BT7"), Order1:=xlAscending, Header:=True
With .Parent.Range("BT6").CurrentRegion
For Each Celda In .Offset(1).Resize(.Rows.Count - 1)
..Parent.Range("AO7") = Celda
With Worksheets.Add(After:=Worksheets(Worksheets.Count) )
..Name = Celda
End With
..Parent.Range("A5").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=.Parent.Range("AO6:AO7"), _
CopyToRange:=Range("A5:AH5")
Next
End With
..Parent.Columns("AO:BT").Clear
Debug.Print .Parent.UsedRange.Address
End With
End Sub


Thanks

I wait your answers

I'd look at these techniques:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

Ricardo_TS wrote:

I have a table where several records come from the A5 to the AH100

From these records I have to generate several new sheets, this would
be an example

Employee Type function
Carlos GT Wash.
Pedro JR Ironing
Pedro JR Fold
Raul KL Deliver
Raul KL Charge
Jose MT Shopping

So I need to be separated into various sheets for employee and full
row

Employee Type function
Carlos GT Wash.

Employee Type function
Pedro JR Ironing
Pedro JR Fold

Employee Type function
Raul KL Deliver
Raul KL Charge

Employee Type function
Jose MT Shopping

I have this code I found on the network and that if I generate new
leaf with the first employee but did not make me much more.

Sub Hoja_x_Auditor_Interno()
Application.ScreenUpdating = False
Dim Celda As Range
With Worksheets("Indicadores Internas").Range("A5").CurrentRegion
.Parent.Range("A6,AH6") = .Cells(1)
.Resize(, 1).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Parent.Range("BT6"), Unique:=True
.Parent.Range("BT6").Sort _
Key1:=.Parent.Range("BT7"), Order1:=xlAscending, Header:=True
With .Parent.Range("BT6").CurrentRegion
For Each Celda In .Offset(1).Resize(.Rows.Count - 1)
.Parent.Range("AO7") = Celda
With Worksheets.Add(After:=Worksheets(Worksheets.Count) )
.Name = Celda
End With
.Parent.Range("A5").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=.Parent.Range("AO6:AO7"), _
CopyToRange:=Range("A5:AH5")
Next
End With
.Parent.Columns("AO:BT").Clear
Debug.Print .Parent.UsedRange.Address
End With
End Sub

Thanks

I wait your answers

--

Dave Peterson

On 24 sep, 10:14, Dave Peterson wrote:
I'd look at these techniques:

Ron de Bruin's EasyFilter addin:http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb





Ricardo_TS wrote:

I have a table where several records come from the A5 to the AH100

From these records I have to generate several new sheets, this would
be an example

Employee * * * *Type * * * * *function
Carlos * * * * * * * GT * * * * * *Wash.
Pedro * * * * * * * *JR * * * * * *Ironing
Pedro * * * * * * * *JR * * * * * * *Fold
Raul * * * * * * * * *KL * * * * * Deliver
Raul * * * * * * * * *KL * * * * * Charge
Jose * * * * * * * * MT * * * * *Shopping

So I need to be separated into various sheets for employee and full
row

Employee * * * * Type * * * * * * function
Carlos * * * * * * * GT * * * * * * * *Wash.

Employee * * * * Type * * * * * * function
Pedro * * * * * * * * JR * * * * * * *Ironing
Pedro * * * * * * * * JR * * * * * * *Fold

Employee * * * * Type * * * * * * function
Raul * * * * * * * * *KL * * * * * * * Deliver
Raul * * * * * * * * *KL * * * * * * * Charge

Employee * * * * Type * * * * * *function
Jose * * * * * * * * MT * * * * * * *Shopping

I have this code I found on the network and that if I generate new
leaf with the first employee but did not make me much more.

Sub Hoja_x_Auditor_Interno()
Application.ScreenUpdating = False
Dim Celda As Range
With Worksheets("Indicadores Internas").Range("A5").CurrentRegion
.Parent.Range("A6,AH6") = .Cells(1)
.Resize(, 1).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Parent.Range("BT6"), Unique:=True
.Parent.Range("BT6").Sort _
Key1:=.Parent.Range("BT7"), Order1:=xlAscending, Header:=True
With .Parent.Range("BT6").CurrentRegion
For Each Celda In .Offset(1).Resize(.Rows.Count - 1)
.Parent.Range("AO7") = Celda
With Worksheets.Add(After:=Worksheets(Worksheets.Count) )
.Name = Celda
End With
.Parent.Range("A5").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=.Parent.Range("AO6:AO7"), _
CopyToRange:=Range("A5:AH5")
Next
End With
.Parent.Columns("AO:BT").Clear
Debug.Print .Parent.UsedRange.Address
End With
End Sub

Thanks

I wait your answers

--

Dave Peterson- Ocultar texto de la cita -

- Mostrar texto de la cita -

Thanks Dave, I watch the techniques, and i tell you what happen