Automatically move entire rows from one Worksheet into another Worksheet in the same Workbook

[joeyjay]

I have a Task List Workbook (with 2 Worksheets)

Worksheet 1 will be just for Open Task Items. Worksheet 2 will be just for Closed Items.

Worksheet 1 will consist of rows of Open Items.

The last column for each row on Worksheet 1 will either be a checkbox (for task completed) or a cell that we type a "completed date" into.

Once the last column cell is checked as completed or the cell is populated with a complete date, is there a way to have that be the trigger for the entire row to transfer over (be cut) from Worksheet to Worksheet 2 of the same Workbook?

Again, Worksheet 1 will be just for Open Task Items and Worksheet 2 will be just for Closed Items.

Thank you for your help.

 

[dsargent]

Yep - but to ensure the code works irrespective of which case is used (i.e. either "GOOD", "good" or "Good" - or any mix) it pays to convert the string entered to one case and then test that result, viz:

If Ucase(Target) = "GOOD" Then

Hello, I would very much appreciate some help with tweaking this code:

I have utilised the previous code which automatically moves a line from "sheet1" to "sheet4" when the sheet1 rngTrigger field is set to "quoted"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Sheet4.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 entred is a date or is recognizable as a valid date
If Target = "Quoted" 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 If
End Sub

However I would also like to automatically move a line from "sheet1" to "sheet5" when the sheet1 rngTrigger is set to "won". As such I amended the code to the following but it does not work - I know I'm missing something somewhere but what?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest, rngDest2 As Range
Set rngDest = Sheet4.Range("rngDest")
Set rngDest2 = Sheet5.Range("rngDest2")
' 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 entred is a date or is recognizable as a valid date
If Target = "Quoted" Then
If Target = "Won" 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 If
End Sub

I have also tried duplicating the original code - with no joy

I also tried this in line 2: Dim rngDest, rngDest2, myMultipleRange As Range - with no joy

If you can point me in the right direction I would be very grateful.

 

[BigC]

You've got part way there, but missed a few bits. You need to work though each line of code to see what it's doing in order to work out which ones need changing and what additional lines you need.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngTrigger As Range
Dim rngDest As Range
Dim rngDest1 As Range
Dim rngDest2 As Range

Set rngTrigger = Sheet1.Range("rngTrigger")
Set rngDest1 = Sheet4.Range("rngDest")
Set rngDest2 = Sheet5.Range("rngDest2")

' Limit the trap area to range of cells in which "Quoted" or "Won" are entered
If Not Intersect(Target, rngTrigger) Is Nothing Then

'Switch destination range according to trigger entered
Select Case Target.Value
 Case "Quoted"
   Set rngDest= rngDest1
 Case "Won"
   Set rngDest= rngDest2
End Select

'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 Sub

I haven't tested this code, so it may not work first time - but give it a go and post back with result/s.

 

[dsargent]

Hi again, thanks for your input BigC, for a moment I thought I had cracked it, alas . . .

I tried your revised code in post #42, thanks for your help on this it highlighted a couple of areas where I was missing elements of code.

I couldn't get it to work as it is but by tweaking a couple of elements of the code with regard the rngDest numbering things looked promising.

In all the attempts I tried I could move items from sheet1 to sheet4 ("Quoted"), but when I tried to add a second instruction to move from sheet1 to sheet5 ("Won") it didn't work and in fact stopped items being moved to sheet4.

Playing around I managed to get target items "quoted" and "won" to move from sheet1 to sheet4 but not to sheet5

The debug indicated an issue with line: -
Application.EnableEvents = False
Target.EntireRow.Select
Selection.Cut
rngDest.Insert Shift:=xlDown
Selection.Delete

but try as I might I could not code around this.


As an alternative I tried another approach which was; move everything under "quoted" from sheet1 to sheet4 using code I know works, then use the same code with numbered rngDest and numbered rngtrigger to move "won" items from sheet4 to sheet5

this didn't work either (sad face). Is it not possible to use Private Sub on separate worksheets within one workbook?

Many thanks

 

[BigC]

Hi dsargent

As I said in my last post, I hadn't tested the code, so am not surprised there was/is a gremlin hiding in it.

Let me address your questions/issues in reverse order:

Is it not possible to use Private Sub on separate worksheets within one workbook?

"Ordinary" Private Subs must be stored in and executed from Standard modules - they cannot be located in a Sheet object or the ThisWorkbook object which are reserved for a special type of sub called "event" macros (like the Worksheet_Change event code on this post) For more info about events see this page Events In Excel VBA on Chip Pearson's website.

It is quite feasible to write two separate sequential sub-blocks of code to handle your two scenarios (i.e. "Quoted" and "Won") - though both blocks would sit within the one Worksheet_Change event and use the same Target range, as (I think) your workbook can only have one such sub. The first sub-block could test for "Quoted" (and action if true) and then the second could test for "Won" (as these triggers are mutually exclusive, it would not matter which was first). I tried to write one block of code with "switches" to handle the different triggers as this saves duplicating variables & code.

I don't think the bug is with the section of code you've identified, even though this is where the Debugger said it was. (This is just where it raised it's head!)

Did you create a Defined Name in Sheet5 called "rngDest2" to provide the code with the location to which to move the "Won" rows? If not, then this may well be the cause of the problem, and why no "Won" rows were moved (the code didn't know where to put them!) The structure of Sheet5 needs to be the same as Sheet4.

Another issue may be the case in which your trigger words are entered in the sheet. To overcome this, change the corresponding section of code from my last post to the following, which ensures that the test is done on all upper case characters:

'Switch destination range according to trigger entered
Select Case UCase(Target.Value)
 Case "QUOTED"
   Set rngDest= rngDest1
 Case "WON"
   Set rngDest= rngDest2
End Select

See if this gets you going.

 

[Jrowell3]

Big C,

I've been working through this thread trying to teach myself the basics but have come up against a block trying to get the code below to work. When I first used it It partially worked by sending data from the "all" sheet to the "under" sheet but it should have gone to the "non" sheet.
I've checked that I have named the sheets and triggers / destinations correctly as per previous posts but since I tried tweeking the code nothing will happen.
Any suggestions greatly received:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngTrigger As Range
Dim rngDest As Range
Dim rngDest2 As Range
Dim rngDest3 As Range
Set rngTrigger = all.Range("rngTrigger")
Set rngDest1 = non.Range("rngDest")
Set rngDest2 = sens.Range("rngDest2")
Set rngDest3 = under.Range("rngDest3")
' Limit the trap area to range of cells in which "non" or "sens" or "under" are entered
If Not Intersect(Target, rngTrigger) Is Nothing Then
'Switch destination range according to trigger entered
Select Case Target.Value
Case "non"
Set rngDest = rngDest
Case "sens"
Set rngDest = rngDest2
Case "under"
Set rngDest = rngDest3
End Select
'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.Copy
rngDest.Insert Shift:=xlDown
Selection.Copy
' Reset EnableEvents
Application.EnableEvents = True
End If
End Sub

Regards,
J

 

[BigC]

Hi J

From a quick review it looks like you haven't got your ranges & variables correctly declared and/or aligned.

1. Where is rngDest1 in this declarations section?

Dim rngTrigger As Range
Dim rngDest As Range
Dim rngDest2 As Range
Dim rngDest3 As Range

2. Do these statements correctly assign the Excel range to the VBA variable? I've not seen a range assignment written like all.Range("rngTrigger") before (i.e. with the bare sheet name (e.g. "all") followed by the period. Normally I use Worksheets("All").range("rngTrigger")

Set rngTrigger = all.Range("rngTrigger")
Set rngDest1 = non.Range("rngDest")
Set rngDest2 = sens.Range("rngDest2")
Set rngDest3 = under.Range("rngDest3")

3. Shouldn't the 3rd line below be "Set rngDest = rngDest1" rather than "Set rngDest = rngDest"?

Select Case Target.Value
Case "non"
Set rngDest = rngDest
Case "sens"
Set rngDest = rngDest2
Case "under"
Set rngDest = rngDest3
End Select

Try using VBA's debugging tools to step through the code. Add a Breakpoint at the 1st "Set" statement to suspend execution of the Worksheet_Change event once it has been triggered by you entering a value into the target range, and then use F8 to step through each line of code, checking what values get assigned to each component as you do so.

If the above doesn't get your code working, come back with specific details as to what happened. (e.g. "I did this and then the code did this.")

Cheers

 

[Michael M]

Just an added note for interests sake....
Rather than either converting the case of the required text or allowing for ALL versions, simply put

Option Compare Text

before the start of the Sub
It will then cover all variations of the required text.....


@BigC, let me know when the addin comes out.....you'll be an IT millionaire in no time....

 

[BigC]

@BigC, let me know when the addin comes out.....you'll be an IT millionaire in no time....

Ha - if only!!ray:

 

[Jrowell3]

Big C,

I've made the suggested changes and the code is working, moving entries from one sheet to another. However if I press enter having made an entry, rather than using the arrow keys to move around a sheet, I get error messages and the code stops working!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngTrigger As Range
Dim rngDest1 As Range
Dim rngDest2 As Range
Dim rngDest3 As Range
Set rngTrigger = Sheet1.Range("rngTrigger")
Set rngDest1 = Sheet2.Range("rngDest1")
Set rngDest2 = Sheet3.Range("rngDest2")
Set rngDest3 = Sheet4.Range("rngDest3")
' Limit the trap area to range of cells in which "non" or "sens" or "under" are entered
If Not Intersect(Target, rngTrigger) Is Nothing Then
'Switch destination range according to trigger entered
Select Case Target.Value
Case "non"
Set rngDest = rngDest1
Case "sens"
Set rngDest = rngDest2
Case "under"
Set rngDest = rngDest3
End Select
'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.Copy
rngDest.Insert Shift:=xlDown
Selection.Copy
' Reset EnableEvents
Application.EnableEvents = True
End If
End Sub


I get a 'type mismatch' error at Case "non" and an 'object required' at rngDest.Insert.

I think it's nearly there but I don't know how to get around the errors!

J

 

[BigC]

J
Can I respectfully suggest that you do some reading about Variables in VBA (declaration, initialising, and use)

When using variables in VBA it is best if you first declare them (in this case we're using the DIM statement) to define them and make them available in the sub.

You can encounter problems if you use a variable that has not been declared or initialised. To provide a pre-running check/detector for this insert "Option Explicit" at the top of each Module - this forces you to declare all variables. Click here to check out Chip Pearson's article 'Declaring Variables' that explains this better than I can.​

Next we need to initialise them (assign them to an object, constant, string, etc.) - which for Ranges and some other objects requires the SET statement.
Now they are available for use.

Follow your code through systematically (Trace the various logic flows/branches) to check that you've properly declared and initialised each variable.

In this application there are five (5) Range object variables used:

1. rngTrigger - the range in which trigger values are entered,
2. rngDest1 - destination for 1st trigger value,
3. rngDest2 - destination for 2nd trigger value,
4. rngDest3 - destination for 3rd trigger value, AND
5. rngDest - a "common use" variable to which the three destination variables (1-3) are pointed at (so only one lot of code is required for whatever destination is required to handle the trigger value)

rngDest is the one you've missed (you had it in your first version of the code, but then replaced it with rngDest1, instead of just adding the latter.)

Add the following line, and you should be up and running:

Dim rngDest As Range​