Move row from one sheet to another automatically

Berville141

New Member
Joined
Oct 1, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,029
Office Version
  1. 365
Platform
  1. Windows
To format the code yourself you need to use the RICH code tags & not the VBA tags.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
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.

Rich (BB code):
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:

VBA Code:
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

New Member
Joined
Oct 27, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Maybe something like this:

VBA Code:
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.
 

camronmartin

New Member
Joined
Oct 28, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Oct 28, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
    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

New Member
Joined
Oct 27, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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?

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top