VBA/Macro help

[WYMMIY]

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.!!!

 

[Dave Peterson]

Check your other post.

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.!!!

 

[Joel]

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

 

[Joel]

I couldn't find any other posting at the microsoft site. Is this request
posted at a different site? People shouldn't post at multiple sites.

 

[Dave Peterson]

Check:

From: Mistry <[email protected]>
Newsgroups: microsoft.public.excel
Subject: Create new worksheet dependant on data
Date: Mon, 6 Jul 2009 04:18:49 -0700 (PDT)