Moving rows between tabs

[propolis]

Hi,

In my attached spreadsheet the column status has a validation option of Open, On Hold and Closed

When I select Open, it stays on that sheet. When I click On Hold, it moves the complete row to the On Hold tab. The same goes if I select Closed.

Now when the row has been move to On Hold, and it came fro Risk Tab, I the click Open it should move the record back to the risk Tab.
The same applies if there is a row that came from Issues tab, and is in th On Hold tab, if I click Open it should move it back to the Issues tabs

Hope this makes sense

Eddie

 

[propolis]

Then the second line within the CheckOnStatusChange procedure has to be changed. That line currently reads:

    If Not Application.Intersect(argTarget, argSht.Columns("C")) Is Nothing Then

When you have replaced ("C") with ("D") you've made the right adjustment.

Thank you, that works

Eddie

 

[GWteB]

You are welcome and thanks for letting us know

 

[vcoolio]

Hello Eddie,

Good to see that you have it working with GWteb's code.
I don't understand why you are having odd behaviour with the code I supplied. The only things I can think of is that you are attempting to run the code from a standard module rather than the ThisWorkbook module or you made some changes which don't work correctly.

I've attached a mock-up of your workbook (how I believe it's set up) with the code implemented here. You'll note that it works as per your previous descriptions.

Cheerio,
vcoolio.

 

[propolis]

You are welcome and thanks for letting us know

Hi,

I copied the code into my big spreadsheet I have, and change the C column to D column.

Now I selected R001 to go to Close. Thats Works. When I am in the close tab, I select On Hold, that works. Now when I click Open nothing happens. My big spreadsheet has 14 columns, and conditional formatting on it, so not sure why it wont move back

Image 1 shows R001 row. I select Close and in image 2 you will see it says closed and its in the closed tab. In image 3 you can see i select Open. In image 4 its still in the Closed tab, not moved to Risk tab and column D it says Open

IF

I just make a simple spreadsheet with the 6 tabs, and enter raw data, it work fine.

Any suggestion you may have will be much appreciated

Eddie

 

[GWteB]

IF I just make a simple spreadsheet with the 6 tabs, and enter raw data, it work fine.

That's a confirmation that the code works. I did some tests and I'm not able to reproduce your issue.
This means that there's something with your other workbook that is causing the behaviour you experience.

The most obvious cause is that the worksheet name "Risk" contains a space you're not aware of.
Check this manually or run the code below once, so all worksheets are corrected for leading and trailing spaces.

Sub TrimSheetNames()
    Dim Sht As Worksheet
    For Each Sht In ThisWorkbook.Worksheets
        Sht.Name = VBA.Trim$(Sht.Name)
    Next Sht
End Sub

 

[propolis]

That's a confirmation that the code works. I did some tests and I'm not able to reproduce your issue.
This means that there's something with your other workbook that is causing the behaviour you experience.

The most obvious cause is that the worksheet name "Risk" contains a space you're not aware of.
Check this manually or run the code below once, so all worksheets are corrected for leading and trailing spaces.

Sub TrimSheetNames()
    Dim Sht As Worksheet
    For Each Sht In ThisWorkbook.Worksheets
        Sht.Name = VBA.Trim$(Sht.Name)
    Next Sht
End Sub

Hi,

I think its my conditional formatting that breaks the move from closed or on hold back to the tab it came from originally

Eddie

 

[GWteB]

I think its my conditional formatting that breaks the move from closed or on hold back to the tab it came from originally

I don't think so, because the "move" of the desired row consists of two separate actions:
1. a copy action of that row to the target sheet, followed by ...
2. a delete action of that row on the current sheet.

Neither of these two actions can be stopped by any CF.
If you use worksheet protection then it goes wrong, but then you should have got a run-time error message from VBA.
What you're not warned about is, neither by VBA nor by a custom message, if a worksheet with a certain name does not exist. That's why I suggested to check on your worksheet names.
In addition to that, also check the elements of the validation drop downs. They need to be exact as in the VBA code.

 

[propolis]

I don't think so, because the "move" of the desired row consists of two separate actions:
1. a copy action of that row to the target sheet, followed by ...
2. a delete action of that row on the current sheet.

Neither of these two actions can be stopped by any CF.
If you use worksheet protection then it goes wrong, but then you should have got a run-time error message from VBA.
What you're not warned about is, neither by VBA nor by a custom message, if a worksheet with a certain name does not exist. That's why I suggested to check on your worksheet names.
In addition to that, also check the elements of the validation drop downs. They need to be exact as in the VBA code.

Thank you for reply.
I checked tabs names - thats all OK.
I typed one row of data by hand and that works without CF. I am now putting CF back as you mentioned it should not break anything.

The anoying thing is I tried to copy the row data from my current sheet into this sheet, and that fails. Type in by hand OK, not a problem as I only have about 30 rows to do.

Will post update

 

[propolis]

Thank you for reply.
I checked tabs names - thats all OK.
I typed one row of data by hand and that works without CF. I am now putting CF back as you mentioned it should not break anything.

The anoying thing is I tried to copy the row data from my current sheet into this sheet, and that fails. Type in by hand OK, not a problem as I only have about 30 rows to do.

Will post update

Update:

I have entered the rows manually, added all cell formulas and all CF. Thank you for the help and assistance

Eddie

 

[GWteB]

Glad to help. Hopefully everything is now working as required. Thank you for the follow-up