Move row from one sheet to another automatically

[Berville141]

Hi
I have a work excel where I need to move a row from one sheet to another once that row has been marked completed.

The first sheet is called WIP and second is called COMPLETED.

The column which is marked as Completed when the task is done is column L
And I would like the row to move to the COMPLETED sheet on row 2 and moving the row currently in there down and so on.

Is this something that can be done?

Appreciate your help!

 

[Fluff]

To format the code yourself you need to use the RICH code tags & not the VBA tags.

 

[VBasic2008]

So I am using this exact script that you provided the OP for something similar, but I want to step it up a notch. So I have a completed tab as well and that part of the script works flawlessly. Here is where I run into an error. I also want to have a column that once a certain drop-down is selected, in this case, "Video Studio" is selected that row is duplicated on a new tab labeled Video. Here is how I tried to attempt that.

Private Sub Worksheet_Change(ByVal Target As Range)
    Macro1 Target   'event runs when cell in Column B is changed
    Macro2 Target   'event runs when cell in Column L is changed
End Sub

Private Sub Macro1(ByVal Target As Range)

    ' Constants
    Const srcFirstRow As Long = 2
    Const tgtName As String = "COMPLETED"
    Const tgtFirstRow As Long = 2
    Const CriteriaColumnIndex As Variant = "B"
    Const Criteria As String = "COMPLETED"
  
    ' Define Criteria Column Range ('rng').
    Dim rng As Range
    Set rng = getColumnRange(Me, CriteriaColumnIndex, srcFirstRow)
    If rng Is Nothing Then Exit Sub ' Criteria Column is 'Empty'.
  
    ' Define Criteria Range ('rng').
    Set rng = Intersect(rng, Target)
    If rng Is Nothing Then Exit Sub ' No changes in Criteria Column Range.
  
    ' Define Transfer Rows ('TRows').
    Dim TRows As Range
    Dim cel As Range
    For Each cel In rng.Cells
        ' vbTextCompare will allow "COMPLETED" or "completed" ...
        If StrComp(cel.Value, Criteria, vbTextCompare) = 0 Then
            collectRows TRows, cel
        End If
    Next cel
    If TRows Is Nothing Then Exit Sub ' Found no cells containing Criteria.
  
    ' Define Target Worksheet ('tgt').
    Dim tgt As Worksheet
    Set tgt = Me.Parent.Worksheets(tgtName)
  
    ' Copy Transfer Rows from Source Worksheet to Target Worksheet (in one go).
    copyToRowOnOtherSheet TRows, tgt, tgtFirstRow
  
    ' Delete Transfer Rows from Source Worksheet (in one go).
    TRows.Delete
  
End Sub

Private Sub Macro2(ByVal Target As Range)

    ' Constants
    Const srcFirstRow As Long = 2
    Const tgtName As String = "VIDEO"
    Const tgtFirstRow As Long = 2
    Const CriteriaColumnIndex As Variant = "L"
    Const Criteria As String = "VIDEO STUDIO"
  
    ' Define Criteria Column Range ('rng').
    Dim rng As Range
    Set rng = getColumnRange(Me, CriteriaColumnIndex, srcFirstRow)
    If rng Is Nothing Then Exit Sub ' Criteria Column is 'Empty'.
  
    ' Define Criteria Range ('rng').
    Set rng = Intersect(rng, Target)
    If rng Is Nothing Then Exit Sub ' No changes in Criteria Column Range.
  
    ' Define Transfer Rows ('TRows').
    Dim TRows As Range
    Dim cel As Range
    For Each cel In rng.Cells
        ' vbTextCompare will allow "VIDEO STUDIO" or "video studio" ...
        If StrComp(cel.Value, Criteria, vbTextCompare) = 0 Then
            collectRows TRows, cel
        End If
    Next cel
    If TRows Is Nothing Then Exit Sub ' Found no cells containing Criteria.
  
    ' Define Target Worksheet ('tgt').
    Dim tgt As Worksheet
    Set tgt = Me.Parent.Worksheets(tgtName)
  
    ' Copy Transfer Rows from Source Worksheet to Target Worksheet (in one go).
    copyToRowOnOtherSheet TRows, tgt, tgtFirstRow
  
End Sub

Now everything works correctly so when I have a row that is in Video Studio it copies just fine to the other tab, but when that tab is eventually marked completed, it still does what it's suppose to do but also throws this error.

Run-time error '1004':
Method 'Intersect' of object'_Global' failed

When I debug it, it highlights the line I have marked in BOLD in the code above. Any help in fixing this would be great. Thank you.

Maybe something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("B")) Is Nothing Then
        Macro1 Target   'event runs when cell in Column B is changed
    End If
    If Not Intersect(Target, Columns("L")) Is Nothing Then
        Macro2 Target   'event runs when cell in Column L is changed
    End If
End Sub

 

[punknwilly]

Maybe something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("B")) Is Nothing Then
        Macro1 Target   'event runs when cell in Column B is changed
    End If
    If Not Intersect(Target, Columns("L")) Is Nothing Then
        Macro2 Target   'event runs when cell in Column L is changed
    End If
End Sub

Am I replacing the script or just adding this? Thank you for your time.

 

[VBasic2008]

Am I replacing the script or just adding this? Thank you for your time.

You are replacing.

 

[camronmartin]

Hi

VBasic2008, could you give instructions on how to implement your VBA? I'm new to excel and never used code before. I am using the same system as the original poster, except my "Completed" column is Column H.​

 

[thpenner1]

I am trying to do the same thing and don't know what script or coding to use.

My rows start at 2-64 and the column = E when the word "complete" is selected from a drop down list I want the entire row to be moved to a separate worksheet. Can anyone help me? Please?

 

[VBasic2008]

Hi

VBasic2008, could you give instructions on how to implement your VBA? I'm new to excel and never used code before. I am using the same system as the original poster, except my "Completed" column is Column H.​

Copy/Paste

The complete code can be pasted into the object module of the Source worksheet. You will open the Visual Basic Editor with CTRL+F11. Then using CTRL+R you will open the project explorer and locate the Source worksheet and double click on it to open its code module. That's where you will paste the code.

The original version of the code used the first code (Private Sub Worksheet_Change(ByVal Target As Range))and three accompanying procedures and was writing the rows one after the other, while the second version is inserting new rows into the second row pushing the others down (the accompanying procedures stay the same).

Change Values

    Const srcFirstRow As Long = 2
    Const tgtName As String = "COMPLETED"
    Const tgtFirstRow As Long = 2
    Const CriteriaColumnIndex As Variant = "L"
    Const Criteria As String = "COMPLETED"

This is where you change stuff: instead of "L" use "H", the first rows are the rows where the data starts (first row below header). src means Source, the worksheet containing the data while tgt means Target (Destination), the first row where you're going to paste the desired rows. tgtName is the Target worksheet name. We are not interested in the srcName, because it will contain the code, where we will use Me.

 

[punknwilly]

You are replacing.

Sorry was away on a family emergency. Ok so I replaced the top portion with your part and the copy part of what I want to happen with Video Studio works great, but when I select Completed it will move and delete like it's supposed to but then throws the same error as before but this time decode shows this line as being the issue?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("B")) Is Nothing Then
        Macro1 Target   'event runs when cell in Column B is changed
    End If
    If Not Intersect(Target, Columns("L")) Is Nothing Then
        Macro2 Target   'event runs when cell in Column L is changed
    End If
End Sub