Moving a row to another sheet when a date is entered

mickymc

New Member
Joined
May 16, 2016
Messages
3
Hi,

I'm trying to get a code to automatically move a row to another sheet when a date is entered. This is very new to me but I've been searching for days and have some kind of code that is bringing up a run-time error '1004'. This also highlights the Set rngTrigger line. Hopefully someone can help.

Code so far;
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngTrigger As Range
Dim rngDest As Range

Set rngTrigger = Sheets("Test1").Range("rngTrigger")
Set rngDest = Sheets("Test2").Range("rngDest")

' Limit the trap area to range of cells in which completed dates are entered as defined above

If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then

' Only trigger if the value entered is a date or is recognizable as a valid date
     If IsDate(Target) Then

'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
        Application.EnableEvents = False
        Target.EntireRow.Select
        Selection.Cut
        rngDest.Insert Shift:=xlDown
        Selection.delete

' Reset EnableEvents
        Application.EnableEvents = True

    End If
End If
End Sub
Thanks in advance
 
Last edited by a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,349
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
What's the text of the error message? (1004 is a generic error number with a number of descriptions and causes)
 

Rhodie72

Well-known Member
Joined
Apr 18, 2016
Messages
546
Some comments first:
You have a nebulous range set up called "rngTrigger". There is no definition of what that is in the code you supplied. As you are new to VBA coding, I'd suggest you learn to define your variables in the code itself first. There are 3 kinds of variables; set, dynamic and declared. Set variables generally only occur within the code and is not relevant to the data in the worksheet. Dynamic variables change according to operations they are involved in. Declared variables are a type that are available to both the worksheets and the code. Other folks may use different terms for them. So if you are going to set up a range on a worksheet, make sure that it is coded into the VBA you have operating on it. This makes it simpler to discover arrors and their source at a later time.

learn about VBA arrays. They can seriously improve your code speed and data manipulation and storage capabilities for working with your data sources. There are static and dynamic arrays.

Learn about the different kinds of loops: do [while|until] loop. for n= x to y ... next n, while ... wend (a very useful conditional loop)

Discover the difference between sub and function.

Find out about Declare, Option, Public, Private and other general statements.

These tips alone will set you back a couple weeks of investigation, but push your abilities forwards many months (or even years) as a result.

Avoid copy and paste functions on the worksheet through VBA because the exact same thing can be done more than 18x faster in code and save the application activation delay time too, which can slow your code down substantially over multiple operations.

Here's a major hint: Store the data from the cell in a VBA variable. check if that variable matches your criteri(a/on), apply action based upon that result.

Good practice would be to write your code out in english so you have a very good idea how to design the basis of what you are attempting to achieve. Example provided:
Code:
Sub Sample_Idea()
'''     DIMENSION VARIABLES (Hence the Dim statement...)

'''     MANAGE ERROR RESULTS
ON ERROR '{RESUME NEXT | GOTO [LABEL]}

'''     DEFINE VARIABLES
'when a date is entered. (State where)


'''     CODE AREA
'get code to automatically move a row to another sheet


Exit Sub
LABEL:

MsgBox Err & " " & Error: Stop
    'This is very new to me but I've been searching for days _
    and have some kind of code that is bringing up _
    run-time error '1004'.
    
    'This also highlights the Set rngTrigger line.
End Sub
 

mickymc

New Member
Joined
May 16, 2016
Messages
3
What's the text of the error message? (1004 is a generic error number with a number of descriptions and causes)
Hi RoryA and Rhodie72,

Thanks for having a look at this.

The error message is;

Run-time error @1004':
Application-defined or object-defined error

Another query I have, do I input 'rngDest' into cell A1 on sheet2? if so where do I put this? Is this all that has to go in sheet2 to define where the new lines go?

Excel is really rewarding when it works but so hard to master.

Thanks again
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi mickymc,

This code requires that you've defined a Named Range called rngTrigger" within the workbook or sheet Test1.

Code:
Set rngTrigger = Sheets("Test1").Range("rngTrigger")
You will get a Run-time error 1004 if a range with that name doesn't exist.
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,278
I would like to see you explain in words what your wanting to do.

If a date is entered into what column?
Copy that row of data to sheet?
And it appears as if you then want the current row deleted
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,278
If your wanting to copy an entire row to Sheet(2) If a date in entered into any cell in column(A)
And then delete that row use this simple little script;

If I'm wrong about the column or sheet number then just modify the script.

To install this code:

Right-click on the sheet tab of the sheet where you plan to enter you Date into column (A)
Select View Code from the pop-up context menu
Paste the below code in the VBA edit window

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Selection.Count > 1 Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
If IsDate(Target.Value) Then Rows(Target.Row).Copy Destination:=Sheets(2).Rows(Lastrow): Rows(Target.Row).Delete
End If
End Sub
 

mickymc

New Member
Joined
May 16, 2016
Messages
3
If your wanting to copy an entire row to Sheet(2) If a date in entered into any cell in column(A)
And then delete that row use this simple little script;

Thanks again for all your help.

"My Answer Is"
This is exactly what I'm trying to do, sorry if I wasn't clear in the first place. This has worked for me but I need to add something else to the sheet object.
I need to move a row if a certain word is used in column "F".
So in this instance if "M3020" is found in column "F" then the code would move the whole row to a certain sheet.

I assume it will similar to the code I already have but somehow state that if a specific word is used it will trigger a move.

Code so far for moving a line to another sheet if a date is entered in column "AE"

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("AE:AE")) Is Nothing Then

If Selection.Count > 1 Then Exit Sub

Dim Lastrow As Long

Lastrow = Sheets("VTC_WIP").Cells(Rows.Count, "A").End(xlUp).Row + 1

If IsDate(Target.Value) Then Rows(Target.Row).Copy Destination:=Sheets("VTC_WIP").Rows(Lastrow): Rows(Target.Row).Delete

End If

End Sub
So the code above runs on sheets ("VTC") and the new code will run on sheets("VTC_WIP"). The idea is if I make a change to column "F" on sheets("VTC_WIP"), this will trigger to move the code to another sheet I have, in this instance could be ("KIA_WIP").

Hope this makes sense.

Thanks in advance everyone.
 
Last edited by a moderator:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,278
This is all very vague.

I need to move a row if a certain word is used in column "F".
So in this instance if "M3020" is found in column "F" then the code would move the whole row to a certain sheet.

How do you plan to tell the script what value in column "F" will cause the row to de copied to sheet ??
So would it be foe example if word is "George" copy to sheet "George"
 

Forum statistics

Threads
1,082,102
Messages
5,363,146
Members
400,720
Latest member
Pettel

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top