Hi all hope this in the right area new to this, I have been using
excel of a while but have no training in it in anyway here is my problem
I have made a form (1st one) to enter the service history of several
mobile plants to a summary list, now I need to sort it from the summary
to indinidual sheets and sort them in order of date on the new sheet.

I have attached a copy of the work book if that helps to explain things


cheers


+-------------------------------------------------------------------+
|Filename: Sample Book2.xlsx |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=286|
+-------------------------------------------------------------------+

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

The web site requires logging on - which requires registration. Sorry, I
can't help you because of that.
If you summarise your problem, it would assist.


"nighttrainrex" wrote:


Hi all hope this in the right area new to this, I have been using
excel of a while but have no training in it in anyway here is my problem
I have made a form (1st one) to enter the service history of several
mobile plants to a summary list, now I need to sort it from the summary
to indinidual sheets and sort them in order of date on the new sheet.

I have attached a copy of the work book if that helps to explain things


cheers


+-------------------------------------------------------------------+
|Filename: Sample Book2.xlsx |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=286|
+-------------------------------------------------------------------+

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

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

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

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

Joel thanks for the reply I not sure if I am placing the code in the
right place I have tried pasting it into VBAproject - This Workbook and
get a runtime error 9 message same when I try it in the summary sheet am
I on the right track do I need to place it in all sheets or am I way
off?

cheers rex




Joel;499456 Wrote:
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: 'The Code Cage Forums - View Profile:
nighttrainrex'
(http://www.thecodecage.com/forumz/member.php?userid=947)
View this thread: 'Sorting Data from ont sheet to several sheets -
The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=137381)




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