| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
