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

Runtime error 9 normally means that the worksheet you have specified
does not exist, it could be mis-spelled or have trailing or leading
spaces.

*How to add and run a Macro*1. *Copy* the macro above placing the
cursor to the left of the code box hold the *CTRL & Left Click,* then
*Right Click* selected code and *Copy.*
2. Open your workbook
3. Press the keys *ALT+F11* to open the Visual Basic Editor
4. Press the keys *ALT+I* to activate the *Insert menu*
5. *Press M* to insert a *Standard Module*
6. *Paste* the code by pressing the keys *CTRL+V*
7. Make any custom changes to the macro if needed at this time.
8. *Save the Macro* by pressing the keys *CTRL+S*
9. Press the keys *ALT+Q* to exit the Editor, and return to Excel.

*To Run the Macro...*
To run the macro from Excel, open the workbook, and press *ALT+F8* to
display the *Run Macro Dialog*. Double Click the macro's name to *Run*
it.
nighttrainrex;500527 Wrote:
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


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=137381

Thanks Simon that helped but still one problem with it, it runs once and
works as required but also brings up following error

Runtime error 9

when I press Debug I get


With Sheets(Plant) highlighted in yellow

going by your note earlier the name is not right so do I need to do
this for each sheet as in Plant 1, Plant 2 ect







Simon Lloyd;500540 Wrote:
Runtime error 9 normally means that the worksheet you have specified
does not exist, it could be mis-spelled or have trailing or leading
spaces.

*How to add and run a Macro*1. *Copy* the macro above placing the
cursor to the left of the code box hold the *CTRL & Left Click,* then
*Right Click* selected code and *Copy.*
2. Open your workbook
3. Press the keys *ALT+F11* to open the Visual Basic Editor
4. Press the keys *ALT+I* to activate the *Insert menu*
5. *Press M* to insert a *Standard Module*
6. *Paste* the code by pressing the keys *CTRL+V*
7. Make any custom changes to the macro if needed at this time.
8. *Save the Macro* by pressing the keys *CTRL+S*
9. Press the keys *ALT+Q* to exit the Editor, and return to Excel.

*To Run the Macro...*
To run the macro from Excel, open the workbook, and press *ALT+F8* to
display the *Run Macro Dialog*. Double Click the macro's name to *Run*
it.



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

You don't need to add it simply CALL it from any point in your original
code so you may have it in the *Private Sub cmdCancel_Click()* sub and
after you close your userform you can
Code:
--------------------
Call SplitSummary
--------------------


nighttrainrex;500609 Wrote:
Thanks Simon that helped it now seems to work fine just wondering can I
add this code to my orginal form code so that it just happens auto or do
I need to run it as a seperate macro

The code for my form is as follows,



Code:
--------------------

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClear_Click()
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

Private Sub cmdOK_Click()
Dim RowCount As Long
Dim ctl As Control
If Me.Txtdate.Value = "" Then
MsgBox "Please enter Date.", vExclamation, "Work Sheet"
Me.Txtdate.SetFocus
End If
If Me.Cboplant.Value = "" Then
MsgBox "Please enter Plant from Dropdown List.", vExclamation, "Work Sheet"
Me.Txtdate.SetFocus
End If
If Me.Txthours.Value = "" Then
MsgBox "Please enter Hours.", vExclamation, "Work Sheet"
Me.Txtdate.SetFocus
End If
If Me.Txtamount.Value = "" Then
MsgBox "Please enter Amount.", vExclamation, "Work Sheet"
Me.Txtamount.SetFocus
End If
If Me.Txtdetails.Value = "" Then
MsgBox "Please enter Details.", vExclamation, "Work Sheet"
Me.Txtdate.SetFocus
End If
If Me.Txtby.Value = "" Then
MsgBox "Please enter Work By.", vExclamation, "Work Sheet"
Me.Txtdate.SetFocus
End If
If Not IsNumeric(Me.Txtamount.Value) Then
MsgBox "Amount Must Contain a Numeric Value.", vbExclamation, "Work Sheet"
Me.Txtamount.SetFocus
Exit Sub
End If
If Not IsDate(Me.Txtdate.Value) Then
MsgBox "Please enter a Valid Date.", vbExclamation, "Work Sheet"
Me.Txtdate.SetFocus
Exit Sub
End If
RowCount = Worksheets("Summary").Range("A1").CurrentRegion.Ro ws.Count
With Worksheets("Summary").Range("A1")
.Offset(RowCount, 0).Value = Me.Txtdate.Value
.Offset(RowCount, 1).Value = Me.Cboplant.Value
.Offset(RowCount, 2).Value = Me.Txthours.Value
.Offset(RowCount, 3).Value = Me.Txtdetails.Value
.Offset(RowCount, 4).Value = Me.Txtamount.Value
.Offset(RowCount, 5).Value = Me.Txtby.Value
.Offset(RowCount, 6).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
End With
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub






Private Sub UserForm_Click()

End Sub

--------------------



--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=137381

Simon again thanks that works fine but again just one mor thing can it
be made to sort only new entrys at the moment if no new entry is made it
adds the last entry to the indevidual sheets

rex


Simon Lloyd;500621 Wrote:
You don't need to add it simply CALL it from any point in your original
code so you may have it in the *Private Sub cmdCancel_Click()* sub and
after you close your userform you can
Code:
--------------------
Call SplitSummary
--------------------



--
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 haven't looked at your attachment, if you are entering dates then you
sort by the date field either ascending or descending depending on your
preference.

nighttrainrex;500630 Wrote:
Simon again thanks that works fine but again just one mor thing can it
be made to sort only new entrys at the moment if no new entry is made it
adds the last entry to the indevidual sheets

rex


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=137381

Simon and all answered my call Thanks very much you help has been
great:Bgr

Simon Lloyd;500632 Wrote:
I haven't looked at your attachment, if you are entering dates then you
sort by the date field either ascending or descending depending on your
preference.


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