Help with Excel

  • Thread starter Thread starter perl
  • Start date Start date
P

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.
 
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
 
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
 
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
 
Wow! made my day. Thanks a mil " it worked"


Bernie Deitrick said:
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
Click to expand...
 
Back
Top