Moving items between sheets based on status change

[Kris Nicole]

Hello,

I want to have a set of 4 live sheets within one Excel Workbook categorizing the status of an order: processing, ready, shipped, delivered. When the status of an order is updated, I would like orders to be automatically moved between sheets. What steps must be taken in order to achieve this?

For example, when an order changes from processing to ready, and I indicate that in Column A, could it be automatically cut from the “processing” sheet and pasted into the next available row of the “ready” sheet?

Thanks,
Kris

 

[vcoolio]

Hello Kris Nicole,

Does this mean that once a row of data is transferred to the "Ready" sheet and the order is later ready to be shipped, does the row of data need to be transferred to the "Shipped" sheet and deleted from the "Ready" sheet? The same from "Shipped" to "Delivered"?
Please clarify this for us.

Cheerio,
vcoolio.

 

[Kris Nicole]

Hi vcoolio,

Thanks for the question. The actual categories for my workbook will not be ready, processing, shipped, and delivered - I used that as an example for familiarity. I will be using it in the medical world to track patients. You’re question made me realize that I chose a poor example, because with ready, processing, shipping, delivered items would always move linearly. In the workbook I’m trying to create, that wold not be the case. To answer your question, yes - items would need to have the ability to be moved between all sheets based on status. BUT, for my actual project it wouldn’t be linear.

I would have 5 sheets based on how close a patient is to being discharged. The sheets would be different respiratory statuses (i.e intubated, bipap, cpap, nc, ready). A patient may start off “intubated,” and then be moved to “bipap,” and then be moved back to “intubated”, and then be on “cpap,” then “nc, then “ready.” For every patient the course would be different. For example one patient may start off in “cpap,” and then only move “ready.” My hope is that when I change the status of a patient to one of the other categories, it would move that row to the appropriate sheet. All items in all sheets would have the ability to move to any sheet indicated by a status change.

Hope that makes sense. I was trying to make it easier to understand but in doing so I oversimplified the concept.

Thanks!!

 

[vcoolio]

Hello Kris,

Ok, try the following code placed in the ThisWorkbook module:-

Option Compare Text
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

        If Intersect(Target, Sh.Columns(1)) Is Nothing Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        If Target.Value = vbNullString Then Exit Sub

Application.ScreenUpdating = False

        Range(Cells(Target.Row, "B"), Cells(Target.Row, "H")).Copy
        Sheets(Target.Value).Range("B" & Rows.Count).End(3)(2).PasteSpecial xlValues
        Target.EntireRow.Delete

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I'm assuming:-
- You wish for this to work on any worksheet.
- In each worksheet, data starts in row2 with headings in Row1.
- You may have data validation drop downs in each cell in Column A of each worksheet with each type of respiratory status listed.
- Once a row of data is transferred to a different worksheet, that row of data is then deleted from the source worksheet.
- The number of columns of data stretch out to Column H. You may have to change this in the code to suit.
- The worksheet names exactly match the respiratory status in the drop downs.

To implement this code:-
- Right click on the tab of the first worksheet.
- Select "View Code" from the menu that appears. You have now opened the VB Editor.
- Over to the left in the Project Explorer, double click on "ThisWorkbook".
- In the big white code field, paste the above code.

Each time that you select a value from any drop down in any worksheet, the relevant row of data will be transferred to the matching respiratory worksheet.
The row of data will be deleted from the source worksheet.

Please test this code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

 

[Kris Nicole]

Everything is moving appropriately. However, I am no longer able to add new patients to column A of any sheet using the data validation drop down that I created on every sheet with respiratory status. I could only move the ones that existed in the workbook before I placed the code.

Any suggestions?

 

[vcoolio]

Hello Kris,

I don't follow.
Why have you placed patient names in Column A when you previously stated that Column A has the different types of respiratory status which you use to move rows of data to the relevant worksheet?
You need to have a separate column for patient names and a separate column for the status.
Uploading a sample of your workbook will help to clarify what you wish to achieve so upload a sample of your workbook to a free file sharing site such as ge.tt or Drop Box and then post the link to your file back here. If your data is sensitive then please use dummy data. Make sure that the sample is an exact replica of your actual workbook.

Cheerio,
vcoolio.

 

[Kris Nicole]

Sample Log.xlsm

Shared with Dropbox

www.dropbox.com

That is a link to the sample document. You can no longer add new entries into column A. Also, when entires move between tabs, column A does not populate.

 

[vcoolio]

Hello Kris,

Here is the code again, slightly altered:-

Option Compare Text
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

        If Intersect(Target, Sh.Columns(1)) Is Nothing Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        If Target.Value = vbNullString Then Exit Sub

Application.ScreenUpdating = False

        Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2)
        Target.EntireRow.Delete

Application.ScreenUpdating = True

End Sub

I've tested it on the sample you uploaded and all works as it should.

"You can no longer add new entries into column A."

All cells in Column A of of each worksheet in your workbook have data validation lists.
Excel will not allow you to type over data validated cells. If you need to add to the list in a validated cell, you need to go back to the Data tab>Data Tools Group>select Data Validation then follow the procedure as you have previously to add to the list. You'll need to select all the cells in Column A first.
To enter new patient details, start your entries in Column B and when your entries are complete, select the required Status in Column A even if the details need to remain on the same sheet (the active sheet).

Cheerio,
vcoolio.

 

[Kris Nicole]

Thank you so much - it works perfectly! Do you know how to allow for multiple selections in a data validation drop-down list?

 

[vcoolio]

You're welcome Kris. I'm glad that I was able to assist.

As to your question, to do what you ask generally comes with the answer "no". However, you can use VBA as a work-around. When you have a few minutes to spare, sit down and watch/read this very informative tutorial from Trump Excel:-
Trump Excel.

Cheerio,
vcoolio.