I copied are the columns instead of skipping the column with the plant name.
the code below fixes this problem.

Sub SplitSummary()


With Sheets("Summary")
RowCount = 2
Do While .Range("A" & RowCount) < ""
Plant = .Range("B" & RowCount)

With Sheets(Plant)
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Newrow = LastRow + 1
Sheets("Summary").Range("A" & RowCount).Copy _
Destination:=.Range("A" & Newrow)
Sheets("Summary").Range("C" & RowCount & ":F" & RowCount).Copy _
Destination:=.Range("B" & Newrow)
End With

RowCount = RowCount + 1
Loop
End With

For Each sht In Sheets
If sht.Name < "Summary" Then

LastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
sht.Rows("4:" & LastRow).Sort _
header:=xlNo, _
key1:=sht.Range("A4"), _
order1:=xlAscending
End If

Next sht

End Sub

"Joel" wrote:

Try this code. Make sure the plant names in column B match the sheet names.
One of the names in the summary sheet is missing a space between Plant and
the number of the plant.


Sub SplitSummary()

With Sheets("Summary")
RowCount = 2
Do While .Range("A" & RowCount) < ""
Plant = .Range("B" & RowCount)

With Sheets(Plant)
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRowCount = LastRow + 1
Set NewRow = .Rows(NewRowCount)
End With

.Rows(RowCount).Copy Destination:=NewRow
RowCount = RowCount + 1
Loop
End With

For Each Sht In Sheets
If Sht.Name < "Summary" Then
LastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row
Sht.Rows("4:" & LastRow).Sort _
header:=xlNo, _
key1:=Sht.Range("A4"), _
order1:=xlAscending
End If

Next Sht

End Sub




"nighttrainrex" wrote:


Hi Patrick thanks for the resopnce I hope the following is ok

I have to enter the data for service history of about 40 mobile plant
Excavators, Dump Trucks ect

I have created a form that enters in the following order from Coloum A
across the page Date, Plant Number(Plant 1), Cost of repair in $,
repairs carried out, By Who & Date entered.

The data for all Plant is entered to Sheet1 renamed Summary in no
paticular order as it is as repairs / servicing are done.

What I would like to do is sort from the summary list to indivdual
sheets for each plant IE: Plant 1, Plant 2 ect

cheers rex


--
nighttrainrex
------------------------------------------------------------------------
nighttrainrex's Profile: http://www.thecodecage.com/forumz/member.php?userid=947
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=137381