There are lots of way to do this. I sorted the original data and then move
the items with the same name to a new worksheet. The code assumes the sheet
names don't exist.


Sub splitdata()

With ActiveSheet

'sort data by colun D
LastRow = .Range("D" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
header:=xlNo, _
key1:=.Range("D1"), _
order1:=xlAscending

RowCount = 1
StartRow = RowCount
Do While .Range("D" & RowCount) < ""
If .Range("D" & RowCount) < .Range("D" & (RowCount + 1)) Then
SheetName = .Range("D" & RowCount)
Set NewSht = Sheets.Add
NewSht.Name = SheetName
.Rows(StartRow & ":" & RowCount).Copy _
Destination:=NewSht.Rows(1)
StartRow = RowCount + 1
End If
RowCount = RowCount + 1
Loop
End With

End Sub


"WYMMIY" wrote:

All

I have a worksheet which has various col, what I would like to do is
to be able to create a new worksheet dependant on data in COL D so
for
example i would like to be able to split Apples into one worksheet
and
then Pears ( along with the 40 other cols which relate to apples or
pears)


184 apple
207 apple
208 apple
209 apple
184 Pears
207 Pears
208 Pears
209 Pears


At first i thought pivot table but as i have to do this twice a month
i thought VBA/marco - the previous person used to copy and paste and
i
think that just a waste of time.!!!