I got the code for anyone that's interested here it is; If you can use it change the column from "H" to what you need and change the word "Class" to the header on the sheet you have. Also, put the sheet name on the tab, it runs smoother and faster. The green text are explanations and the red is what you should modify to suit your needs.
Sub Split_A()
'This is to trim/clean all the excess trailing and leading spaces
'including white spaces because data was retrieved from another software
Sheets("HES Backorder Details").Select
Columns("A:O").Select
Columns("A:O").EntireColumn.AutoFit
Dim r As Range
For Each r In ActiveSheet.UsedRange
v = r.Value
If v <> "" Then
If Not r.HasFormula Then
r.Value = Trim(v)
End If
End If
Next r
'this is to remove all symbols that cannot be used on tabs - /,\,[,]... etc
Selection.Replace What:="/", Replacement:="-", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="\", Replacement:="-", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'this is to split the data by the column you choose in this case column "H-Class",
'change to the column you want and replace the word "Class" with the header on your document
Const col = "H"
Const header_row = 1
Const starting_row = 2
Dim source_sheet As Worksheet
Dim destination_sheet As Worksheet
Dim source_row As Long
Dim last_row As Long
Dim destination_row As Long
Dim Class As String
Set source_sheet = ActiveSheet
last_row = source_sheet.Cells(source_sheet.Rows.Count, col).End(xlUp).Row
For source_row = starting_row To last_row
Class = source_sheet.Cells(source_row, col).Value
Set destination_sheet = Nothing
On Error Resume Next
Set destination_sheet = Worksheets(Class)
On Error GoTo 0
If destination_sheet Is Nothing Then
Set destination_sheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
destination_sheet.Name = Class
source_sheet.Rows(header_row).Copy destination:=destination_sheet.Rows(header_row)
End If
destination_row = destination_sheet.Cells(destination_sheet.Rows.Count, col).End(xlUp).Row + 1
source_sheet.Rows(source_row).Copy destination:=destination_sheet.Rows(destination_row)
Next source_row
'This is to autofit all sheets and hide what is not required
Sheets(Array("HES Backorder Details", "Romsons International (Inst)", _
"Bd De Mexico (Inst)", "Intersurgical Limited (Inst)", "Baxter (Inst)", _
"Kodak (Inst)", "Roche Diagnostics (Inst)", "Merlin Medical (Inst)", _
"Neotech Products (Inst.)", "Tyco H-Care Inst. Medtronic Pr", _
"Sklar Instruments (Inst)", "Medtronics Logistics Us", _
"Ace H-Care- Sanacare (Inst)", "Alcon Centroamerica (Inst.)", "Pajunk (Inst.)", _
"Merit (Inst.)", "Bsn Medical La (Inst.)", "Advance Medgroup-Zimmer (Inst)", _
"Service (Inst)", "Teleflex Medical (Inst)", "Miscellaneous Hes (Inst)", _
"Utah Medical Products (Inst)", "Argon Medical Devices (Inst)", _
"Rockwell Medical Tech (Inst)", "Electroplast (Inst)")).Select
Sheets("Bd De Mexico (Inst)").Activate
Sheets(Array("Baxter (Govt)", "Intas Pharma (Govt)", "Rockwell (Govt)", _
"Masters Pharmaceuticals (Govt)", "Alcon Gov'T", "Janssen Cilag (Govt)", _
"Bsn Medical La (Govt)", "Genzyme Corporation (Govt)", "Romsons (Govt)", _
"Tyco H-Care (Gov) Medtronic Pr", "Idis Limited (Govt)", "Novartis Pharma (Govt)" _
, "Sandoz (Govt.)", "Roche Diagnostics (Govt)", "Kodak (Govt)")).Select Replace _
:=False
Cells.Select
Cells.EntireColumn.AutoFit
Columns("E:E").Select
Selection.EntireColumn.Hidden = True
Columns("K:K").Select
Selection.EntireColumn.Hidden = True
Columns("O:O").Select
Selection.EntireColumn.Hidden = True
End Sub
