Moving a row to another sheet when a date is entered

[mickymc]

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;

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

 

[RoryA]

What's the text of the error message? (1004 is a generic error number with a number of descriptions and causes)

 

[Rhodie72]

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:

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]

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]

Hi mickymc,

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

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

You will get a Run-time error 1004 if a range with that name doesn't exist.

 

[My Aswer Is This]

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]

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

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]

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"

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.

 

[My Aswer Is This]

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"