Help with Excel

[perl]

Hi,

I am currently working on an excel worksheet for shipping. I would like to
create a function that once the item status is marked "shipped" the entire
line should move onto a different excel sheet named shipped orders. can
anybody be of help I really would appreciate it.

 

[Gary''s Student]

Which column would you use for "shipped" status??

 

[perl]

Column L

Which column would you use for "shipped" status??

 

[Bernie Deitrick]

perl,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If UCase(Trim(Target.Value)) = "SHIPPED" Then
Application.EnableEvents = False
Target.EntireRow.Copy _
Worksheets("Shipped").Cells(Rows.Count, 1).End(xlUp)(2).EntireRow
Target.EntireRow.Delete
Application.EnableEvents = True
End If

End Sub

 

[perl]

i pasted the code however nothing seems to happen

 

[perl]

Hey Bernie

tried it again the following message came up run-time error 9
subscript out of range. there is an option to debug should I click on that?

thanks loads

 

[Bernie Deitrick]

My mistake - I got the name of your storage sheet wrong -I should have used "Shipped Orders". Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If UCase(Trim(Target.Value)) = "SHIPPED" Then
Application.EnableEvents = False
Target.EntireRow.Copy _
Worksheets("Shipped Orders").Cells(Rows.Count, 1).End(xlUp)(2).EntireRow
Target.EntireRow.Delete
Application.EnableEvents = True
End If

End Sub


HTH,
Bernie
MS Excel MVP

 

[perl]

Wow! made my day. Thanks a mil " it worked"

My mistake - I got the name of your storage sheet wrong -I should have used "Shipped Orders". Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If UCase(Trim(Target.Value)) = "SHIPPED" Then
Application.EnableEvents = False
Target.EntireRow.Copy _
Worksheets("Shipped Orders").Cells(Rows.Count, 1).End(xlUp)(2).EntireRow
Target.EntireRow.Delete
Application.EnableEvents = True
End If

End Sub


HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

perl,

Glad to hear it - thanks for letting me know.

Bernie
MS Excel MVP

 

[Ashish Mathur]

Hi,

Try this Quretion 7 of the link -
http://ashishmathur.com/knowledgebaseII.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com