Help with Excel
 

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.

Help with Excel
 

Which column would you use for "shipped" status??
--
Gary''s Student - gsnu200902


"perl" wrote:

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.

Help with Excel
 

Column L

"Gary''s Student" wrote:

Which column would you use for "shipped" status??
--
Gary''s Student - gsnu200902


"perl" wrote:

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.

Help with Excel
 

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" wrote in message
...
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.

Help with Excel
 

i pasted the code however nothing seems to happen

"Bernie Deitrick" wrote:

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" wrote in message
...
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.

Help with Excel
 

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

"perl" wrote:

i pasted the code however nothing seems to happen

"Bernie Deitrick" wrote:

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" wrote in message
...
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.

Help with Excel
 

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" wrote in message
...
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

"perl" wrote:

i pasted the code however nothing seems to happen

"Bernie Deitrick" wrote:

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" wrote in message
...
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.

Help with Excel
 

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


"Bernie Deitrick" wrote:

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" wrote in message
...
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

"perl" wrote:

i pasted the code however nothing seems to happen

"Bernie Deitrick" wrote:

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" wrote in message
...
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.

Help with Excel
 

perl,

Glad to hear it - thanks for letting me know.

Bernie
MS Excel MVP

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

Help with Excel
 

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"perl" wrote in message
...
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.