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

 

[My Aswer Is This]

Well lets just start with this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Put this script in What ever sheets you plan to run the script from.
You mentioned what sheets to copy the row too but never said what sheets you plan to run this script from.

If in column C you enter a sheet name the script will copy this row to the sheet name you selected in the drop down list in column C
If this works we will move on to other things you want.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/14/2021  5:29:03 AM  EST
If Target.Column = 3 Then
On Error GoTo M
Application.EnableEvents = False
Dim r As Long
Dim ans As String
r = Target.Row
ans = Target.Value
Rows(r).Copy Sheets(ans).Rows(2)
Rows(r).Delete

End If
Application.EnableEvents = True
Exit Sub
M:
MsgBox "The sheet named " & ans & "Does not Exist"
Application.EnableEvents = True

End Sub

 

[propolis]

Hi,

Thank you its works one way moving the row. Moving rows from the risk, action, issuses and dependacy tabs to onhold and closed tabs

But it does not do the revese (See attached image) when I am in the Closed or On Hold tab and in either tabs column C, I select validation option - Open, it must do the following :

Now when I go to the Closed Tab, and there is a row in there where in column A it will say R001 and column C shows closed. I now change the value in column C (select validation option Open) and select Open, and because column A says R001 in the Closed Tab, it sees the R, first character and knows it needs to move the row back to the Risk tab

Now when I go to the On Hold Tab, and there is a row in there where in column A it will say A001 and column C shows On hold. I now change the value in column C (select validation option Open) and select Open, and because column A says A001 in the on hold tab, it sees the A, first character and knows it needs to move the row back to the Actions tab

In the Risks Tab, all records in column A will be prefixed like so: R001, R002, R003, etc
In the Ations Tab, all records in column A will be prefixed like so: A001, A002, A003, etc
In the Issues Tab, all records in column A will be prefixed like so: I001, I002, I003, etc
In the Dependancy Tab, all records in column A will be prefixed like so: D001, D002, D003, etc

 

[My Aswer Is This]

I mentioned in my post this would not do all you want, See here:
What I said:
If this works we will move on to other things you want.

But as I look at this more you have maybe a dozen over things you want.
So lets see if someone else on the forum can help out with the rest of what you want.

 

[My Aswer Is This]

So why do we need to look for things like R001
Why not just put Risk the sheet name you want this row copied to.
And again what row on these sheets is the row copied to. and is this row then deleted?

 

[propolis]

OK,

Close this as I dont think we will get a resolution

 

[vcoolio]

Hello Propolis,

This may be what you're after:-

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

        Dim lr As Long, c As Range
        
        If Intersect(Target, Sh.Columns(3)) Is Nothing Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        lr = Sh.Range("A" & Rows.Count).End(xlUp).Row
        
Application.ScreenUpdating = False
Application.EnableEvents = False

        If Target.Value = "On Hold" Or Target.Value = "Closed" Then
                Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2)
                Target.EntireRow.Delete
        ElseIf Target.Value = "Open" Then
                For Each c In Sh.Range("A2:A" & lr)
                        Text = Left(c.Value, 1)
                        Select Case Text
                                Case Is = "R"
                                        c.EntireRow.Copy Sheets("Risk").Range("A" & Rows.Count).End(3)(2)
                                        c.EntireRow.Delete
                                Case Is = "A"
                                        c.EntireRow.Copy Sheets("Action").Range("A" & Rows.Count).End(3)(2)
                                        c.EntireRow.Delete
                                Case Is = "I"
                                        c.EntireRow.Copy Sheets("Issue").Range("A" & Rows.Count).End(3)(2)
                                        c.EntireRow.Delete
                                Case Is = "D"
                                        c.EntireRow.Copy Sheets("Dependency").Range("A" & Rows.Count).End(3)(2)
                                        c.EntireRow.Delete
                        End Select
                Next c
        End If
        
Sh.[A1].CurrentRegion.Offset(1).Sort Sh.[A2], 1
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

      Sh.[A1].CurrentRegion.Offset(1).Sort Sh.[A2], 1
      
End Sub

It's actually two codes with the SheetActivate code simply re-sorting the data for you when you return to a sheet. If you don't think it's necessary then just delete the code.

Both codes need to be placed into the 'ThisWorkbook' module and will hence work on all worksheets.

You'll note my slightly different spelling for 'Dependency' in the code.

I hope that this helps.

Cheerio,
vcoolio.

 

[GWteB]

Had also cobbled together some code. Note the separate procedures which are dependencies of the CheckOnStatusChange procedure. See if you can use it.

This goes in the ThisWorkbook module:

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

    CheckOnStatusChange Sh, Target

End Sub

This goes in a standard module:

Public Sub CheckOnStatusChange(ByVal argSht As Worksheet, ByVal argTarget As Range)
    Dim IDchar As String
    If Not Application.Intersect(argTarget, argSht.Columns("C")) Is Nothing Then
        If Not argTarget.CountLarge > 1 Then
            With argTarget
                Select Case .Value
                Case "Open"
                    If .Parent.Name = "On Hold" Or _
                       .Parent.Name = "Closed" Then
                       
                        IDchar = Left(.Offset(0, -2).Value, 1)
                        Select Case IDchar
                        Case "R": MoveRecord argTarget, "Risk"
                        Case "A": MoveRecord argTarget, "Action"
                        Case "I": MoveRecord argTarget, "Issue"
                        Case "D": MoveRecord argTarget, "Dependency"
                        Case Else
                            'unknown IDchar, do nothing
                        End Select
                    Else
                        ' record is still on its original worksheet so there's nothing to move back
                    End If
                Case "On Hold": MoveRecord argTarget, "On Hold"
                Case "Closed":  MoveRecord argTarget, "Closed"
                Case Else
                    ' Unknown status, do nothing
                End Select
            End With
        End If
    End If
End Sub

Public Sub MoveRecord(ByVal argTarget As Range, ByVal argShtName As String)
    Dim DestSht As Worksheet
    With argTarget
        If WorksheetExists(.Parent.Parent, argShtName, DestSht) Then
            Application.EnableEvents = False
            .EntireRow.Copy DestSht.Range("A" & DestSht.Cells(DestSht.Rows.Count, "A").End(xlUp).Row + 1)
            .EntireRow.Delete
            Application.EnableEvents = True
        End If
    End With
End Sub

Public Function WorksheetExists(ByVal argWb As Workbook, ByVal argShtName As String, ByRef argSht As Worksheet) As Boolean
    On Error Resume Next
    Set argSht = argWb.Worksheets(argShtName)
    WorksheetExists = Not argSht Is Nothing
End Function

 

[propolis]

Hello Propolis,

This may be what you're after:-

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

        Dim lr As Long, c As Range
       
        If Intersect(Target, Sh.Columns(3)) Is Nothing Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        lr = Sh.Range("A" & Rows.Count).End(xlUp).Row
       
Application.ScreenUpdating = False
Application.EnableEvents = False

        If Target.Value = "On Hold" Or Target.Value = "Closed" Then
                Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2)
                Target.EntireRow.Delete
        ElseIf Target.Value = "Open" Then
                For Each c In Sh.Range("A2:A" & lr)
                        Text = Left(c.Value, 1)
                        Select Case Text
                                Case Is = "R"
                                        c.EntireRow.Copy Sheets("Risk").Range("A" & Rows.Count).End(3)(2)
                                        c.EntireRow.Delete
                                Case Is = "A"
                                        c.EntireRow.Copy Sheets("Action").Range("A" & Rows.Count).End(3)(2)
                                        c.EntireRow.Delete
                                Case Is = "I"
                                        c.EntireRow.Copy Sheets("Issue").Range("A" & Rows.Count).End(3)(2)
                                        c.EntireRow.Delete
                                Case Is = "D"
                                        c.EntireRow.Copy Sheets("Dependency").Range("A" & Rows.Count).End(3)(2)
                                        c.EntireRow.Delete
                        End Select
                Next c
        End If
       
Sh.[A1].CurrentRegion.Offset(1).Sort Sh.[A2], 1
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

      Sh.[A1].CurrentRegion.Offset(1).Sort Sh.[A2], 1
     
End Sub

It's actually two codes with the SheetActivate code simply re-sorting the data for you when you return to a sheet. If you don't think it's necessary then just delete the code.

Both codes need to be placed into the 'ThisWorkbook' module and will hence work on all worksheets.

You'll note my slightly different spelling for 'Dependency' in the code.

I hope that this helps.

Cheerio,
vcoolio.

Thank you for the code. Tried it but the moving back from the On Hold and Closed tab has a odd behavious.

I put a 4 tab rows in either On Hold or Closed tab

When I click for eg Risk row thats in the On hold tab and select Open, its goes back to the risk Tab but also moves another row Action as well. Risk is in the Risk Tab and status is Open. The action row that moves as well is in the Action tab but the status still shows On Hold.

Do you need more detail?

Eddie

 

[propolis]

Had also cobbled together some code. Note the separate procedures which are dependencies of the CheckOnStatusChange procedure. See if you can use it.

This goes in the ThisWorkbook module:

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

    CheckOnStatusChange Sh, Target

End Sub

This goes in a standard module:

Public Sub CheckOnStatusChange(ByVal argSht As Worksheet, ByVal argTarget As Range)
    Dim IDchar As String
    If Not Application.Intersect(argTarget, argSht.Columns("C")) Is Nothing Then
        If Not argTarget.CountLarge > 1 Then
            With argTarget
                Select Case .Value
                Case "Open"
                    If .Parent.Name = "On Hold" Or _
                       .Parent.Name = "Closed" Then
                      
                        IDchar = Left(.Offset(0, -2).Value, 1)
                        Select Case IDchar
                        Case "R": MoveRecord argTarget, "Risk"
                        Case "A": MoveRecord argTarget, "Action"
                        Case "I": MoveRecord argTarget, "Issue"
                        Case "D": MoveRecord argTarget, "Dependency"
                        Case Else
                            'unknown IDchar, do nothing
                        End Select
                    Else
                        ' record is still on its original worksheet so there's nothing to move back
                    End If
                Case "On Hold": MoveRecord argTarget, "On Hold"
                Case "Closed":  MoveRecord argTarget, "Closed"
                Case Else
                    ' Unknown status, do nothing
                End Select
            End With
        End If
    End If
End Sub

Public Sub MoveRecord(ByVal argTarget As Range, ByVal argShtName As String)
    Dim DestSht As Worksheet
    With argTarget
        If WorksheetExists(.Parent.Parent, argShtName, DestSht) Then
            Application.EnableEvents = False
            .EntireRow.Copy DestSht.Range("A" & DestSht.Cells(DestSht.Rows.Count, "A").End(xlUp).Row + 1)
            .EntireRow.Delete
            Application.EnableEvents = True
        End If
    End With
End Sub

Public Function WorksheetExists(ByVal argWb As Workbook, ByVal argShtName As String, ByRef argSht As Worksheet) As Boolean
    On Error Resume Next
    Set argSht = argWb.Worksheets(argShtName)
    WorksheetExists = Not argSht Is Nothing
End Function

Hi,

Thank you very much. This work both ways.

What do I need to change if the Status value is in column 4 (D) going forward

Eddie

 

[GWteB]

What do I need to change if the Status value is in column 4 (D) going forward

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.