Combining Macros and if function
 

Hi,

I'm currently working on an excel worksheet for shipping. I've created
myself a worksheet so that every line is a different shipment. My worksheet
has a VBA function that if Cell L1 ( which is the status of shipment)
contains "shipped" the line will automatically jump to a different worksheet
labeled "shipped orders" now I wanted to maximize efficiency and created an
if function that if Cell N1 contains a UPS tracking number then L1 will
receive "shipped". The if function worked however i was dissapointed to see
that the line didn't jump to the next worksheet. could any of you be of help?



Below find my VBA code:

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

thanks in advance,

Perl

Combining Macros and if function
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You send a clear explanation of what you want
3. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"perl" wrote in message
...
Hi,

I'm currently working on an excel worksheet for shipping. I've created
myself a worksheet so that every line is a different shipment. My
worksheet
has a VBA function that if Cell L1 ( which is the status of shipment)
contains "shipped" the line will automatically jump to a different
worksheet
labeled "shipped orders" now I wanted to maximize efficiency and created
an
if function that if Cell N1 contains a UPS tracking number then L1 will
receive "shipped". The if function worked however i was dissapointed to
see
that the line didn't jump to the next worksheet. could any of you be of
help?



Below find my VBA code:

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

thanks in advance,

Perl

Combining Macros and if function
 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 14 Or Target.Row < 2 Then Exit Sub
tr = Target.Row
Cells(tr, "L") = "Shipped"
Cells(tr, "M") = Date
Rows(tr).Copy _
Worksheets("Shipped Orders").Cells(Rows.Count, 1).End(xlUp)(2)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You send a clear explanation of what you want
3. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"perl" wrote in message
...
Hi,

I'm currently working on an excel worksheet for shipping. I've created
myself a worksheet so that every line is a different shipment. My
worksheet
has a VBA function that if Cell L1 ( which is the status of shipment)
contains "shipped" the line will automatically jump to a different
worksheet
labeled "shipped orders" now I wanted to maximize efficiency and created
an
if function that if Cell N1 contains a UPS tracking number then L1 will
receive "shipped". The if function worked however i was dissapointed to
see
that the line didn't jump to the next worksheet. could any of you be of
help?



Below find my VBA code:

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

thanks in advance,

Perl