Split sheet out 0-26

Hi,

I have a wb with 27 client types, 161k rows of data on sheet 1

I would like a macro that can split the data out in ascending order 0,1,2,3,4 etc

So my first tab will be the full data and then the other tabs, starting at 0,1,2 etc etc

Any ideas?

Thanks
Seamus2626Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Sub Filtertosheets()
Dim rng As Range
Dim sws As Worksheet
Set sws = ActiveSheet
Set rng = Range("A1")
'Sheet1.Cells.Copy Sheet2.Cells
rng.EntireColumn.Insert
Range(rng.Offset(, -1), rng.End(xlDown).Offset(, -1)).Formula = "=row()"
Range(rng.Offset(, -1), rng.End(xlDown).Offset(, -1)).Value = Range(rng.Offset(, -1), rng.End(xlDown).Offset(, -1)).Value
rng.CurrentRegion.Sort key1:=rng, order1:=xlAscending, key2:=rng.Offset(, -1), order2:=xlAscending, Header:=xlYes
For i = 0 To 26
Sheets.Add , Sheets(Sheets.Count)
ActiveSheet.Name = "CT" & i
    rng.CurrentRegion.AutoFilter Field:=2, Criteria1:=i
    rng.CurrentRegion.Offset(, 1).SpecialCells(xlCellTypeVisible).Copy Range("A1")
Next i
sws.Select
rng.AutoFilter
rng.CurrentRegion.Sort key1:=rng.Offset(, -1), order1:=xlAscending, Header:=xlYes
rng.Offset(, -1).EntireColumn.Delete
End Sub

Open in new window

0
 
Angelp1ayCommented:
Hi Seamus.

I think we need a little more information about the problem and if possible an example workbook. What are your columns?!?
0
 
Seamus2626Author Commented:
Apologies, i forgot the wb
Client-Types-EE.zip
0
 
Rob HensonFinance AnalystCommented:
0
 
Seamus2626Author Commented:
Hero!
0
All Courses

From novice to tech pro — start learning today.