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.

 

[LBinGA]

Anyone? I'm really stumped.
Also, in my previous post I said I placed it in ThisWorkbook incorrectly. It's in the Marketing Sheet. I've tried a number of other things since this morning and still not getting it to do anything.

Thanks,
LBinGA

I'm sorry to say, I'm having a similar issue with nothing happening. I have the following code in place on ThisWorkbook and it does nothing, no error, nothing. I have Column $D:$D set as rngTrigger on the Marketing sheet & rngDest is set on the Achive sheet to $3:$3. The code is pasted on the Marketing sheet (not in a Module).
Column D is linked to a checkbox in Column C, which allows me to Conditionally Format off the TRUE or FALSE that appears in D, depending on the state of the Check box..
When the Check Box is checked, Column D returns TRUE, signally that the To Do item in that Row is completed. I would like for all Rows with TRUE in Column D, to be moved to the Archive sheet when the check box is checked.

I have more than one sheet that this needs to happen on and I assume I just copy the working code over to each of them, however, I'm not getting a response on just the one sheet as yet.

I've tried setting the Value of the contents in the Linked Cells in Column D to If Target="TRUE" Then, and If Target.Value="TRUE" or If Target=True Then and what you see below. None of them created any type of response.

Could my conditional formatting (it changes the fill color of the date to Green when completed) competing with this code in come way?

Thanks for any help I can get....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Worksheets("Archive").Range("rngDest")
 
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Range("rngTrigger")) Is Nothing Then
 
' Only trigger if the value entered is TRUE
     If UCase(Target) = "TRUE" 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

 

[LBinGA]

Anyone? I'm really stumped.
Also, in my previous post I said I placed it in ThisWorkbook incorrectly. It's in the Marketing Sheet. I've tried a number of other things since this morning and still not getting it to do anything.

Thanks,
LBinGA

Apparently this code will not work with the checkbox linked to a cell. I removed the checkboxes, set it to cut the row at a "Yes" entry and bingo. Worked.

LBinGA

 

[lailazineb]

Hi all,

I have used the code provided to move rows from one sheet to an other in same workbook and it works, but I need to move rows from different worksheets to different destinations is same workbook. There are 4 triggers and 4 destinations.Can you please help? how can I use one code in all workbook to enable moving rows from different sheets.


Thank you

 

[BigC]

Hi lailazineb and welcome to the Board

One of the previous posters to this thread had a similar "fluid" requirement - he had various sheets for different years (year number was used as the sheet/tab name), and wanted to move rows between years - perhaps several times - before eventually to a final "cemetery" sheet. I can't remember which user (as you can see this thread has got some history!!), but if you trawl through the posts you should find details of how we got the application working for him. In a nutshell, you need the code (with multiple conditions to determine the correct destination for the row that triggered the code) on the sheet object of each sheet from which rows will be moved, as well as a defined destination range on each of them as well if those sheets are to receive rows.

 

[lailazineb]

Hi lailazineb and welcome to the Board

One of the previous posters to this thread had a similar "fluid" requirement - he had various sheets for different years (year number was used as the sheet/tab name), and wanted to move rows between years - perhaps several times - before eventually to a final "cemetery" sheet. I can't remember which user (as you can see this thread has got some history!!), but if you trawl through the posts you should find details of how we got the application working for him. In a nutshell, you need the code (with multiple conditions to determine the correct destination for the row that triggered the code) on the sheet object of each sheet from which rows will be moved, as well as a defined destination range on each of them as well if those sheets are to receive rows.

Thank you Big C for your help. your code is very useful and worked for me when I tried to move rows from on sheet which I named " in hanger waiting" to the other 3 sheets. the code I used is the following
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim rngDest1 As Range
Dim rngDest2 As Range
Dim rngDest3 As Range
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 the name of sheet is defined
If Not Intersect(Target, Sheet1.Range("rngTrigger1")) Is Nothing Then
' Only trigger if the value entred is name of the sheet
If Target = "Prepare to take off" 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
rngDest1.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
If Target = "Pre-check" 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
rngDest2.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
If Target = "Stucked unknown landing slot" 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
rngDest3.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
End If

End Sub


[U]
Now I need to apply same code to move rows from sheet 2 to other sheets= sheet1, sheet3 and sheet4 I tried this code but error message appeared as Ambiguous name detected


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim rngDest1 As Range
Dim rngDest2 As Range
Dim rngDest3 As Range
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 the name of sheet is defined
If Not Intersect(Target, Sheet1.Range("rngTrigger1")) Is Nothing Then
' Only trigger if the value entred is name of the sheet
If Target = "Prepare to take off" 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
rngDest1.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
If Target = "Pre-check" 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
rngDest2.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
If Target = "Stucked unknown landing slot" 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
rngDest3.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
End If

End Sub[/COLOR]



Please help

Thank you

 

[lailazineb]

ooops I copied same code twice
the second code I am trying to use in same workbook is the following. This to move rows from sheet 2 to sheet1 but error appeared as as Ambiguous name detected.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim rngDest4 As Range
Set rngDest4 = Sheet1.Range("rngDest4")

' Limit the trap area to range of cells in which the name of sheet is defined
If Not Intersect(Target, Sheet1.Range("rngTrigger2")) Is Nothing Then
' Only trigger if the value entred is name of the sheet
If Target = "In hanger waiting" 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
rngDest4.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If

End If
End Sub


Thank you for your help

 

[BigC]

lailazineb

Have you tried stepping through the code (using a Breakpoint on an early code line to halt the code, and then pressing F8 key) to see which object causes the 'Ambiguous name' error?

I don't know if this is the cause of the error, but if you want to move rows from Sheet2 to Sheet1, your rngTrigger needs to refer to a range on the source sheet (i.e. Sheet2), rather than what you have here:

If Not Intersect(Target, [B][COLOR=#ff0000]Sheet1[/COLOR][/B].Range("rngTrigger2")) Is Nothing Then

If your application is similar to the original, and the multi-destination one I referred to, note that there is only one rngDest on each sheet, so you can use the same Defined Name (i.e. "rngDest") on each sheet by creating the name with SHEET scope rather than Workbook scope. You then just assign the appropriate destination range (with relevant Sheet prefix) to the rngDest variable in the code via the 'Set =' statement. In this way you don't need to repeat the following code for each destination sheet:

Application.EnableEvents = False
 Target.EntireRow.Select
Selection.Cut
rngDest.Insert Shift:=xlDown
Selection.Delete
 ' Reset EnableEvents
Application.EnableEvents = True

All you need to do is determine (I suggest via a Select Case construct) the correct destination, and then use multiple but varying:

Set rngDest = Sheet[number].Range("rngDest")

to match the destination with the trigger value.
Once the correct destination range has been assigned to the rngDest variable, the move code can be executed.

Hope this helps.

 

[Legacy 347579]

First Off - This is exactly what I was looking for and Thanx for the code.

I am also fairly new to adding code to a workbook but I am almost where I need to be so I need to clarify a few things if I can.

My Sheet 1 has approx 600 rows including the Header row. I am using a dropdown list in Column A to Select or Unselect the row (Yes or No). If "Yes" is selected I move the complete row to Sheet 2 with the exact same headers. on Sheet 1 "rnfTrigger1" ='Available Content'!$A$2:$A$600 and on Sheet 2 "rngDest1" ='Property Lineup'!$A$2:$K$2

The Code inserted to Worksheet 1 is

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest1 As Range
Set rngDest1 = Sheet2.Range("rngDest1")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Sheet1.Range("rngTrigger1")) Is Nothing Then
' Only trigger if the value entred is a date or is recognizable as a valid date
If UCase(Target) = "YES" 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
rngDest1.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
End If
End Sub

This is all Working to this point.

Now - I want to set up the same process but in reverse - in case an accidental selection is made or the data needs to be moved back to the first list so I can move the row back from Sheet 2 to Sheet 1

My Sheet 2 Could Have 600 rows including the Header row if everything from sheet 1 was moved over. I am using the same dropdown list in Column A to Select or Unselect the row (Yes or No). If "No" is selected I move the complete row to Sheet 1 with the exact same headers. on Sheet 2 "rnfTrigger2" ='Property Lineup'!$A$2:$A$600 and on Sheet 1 "rngDest2" ='Available Content'!$A$601:$K$601

I add this code to Sheet 2

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest1 As Range
Set rngDest1 = Sheet2.Range("rngDest1")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Sheet1.Range("rngTrigger1")) Is Nothing Then
' Only trigger if the value entred is a date or is recognizable as a valid date
If UCase(Target) = "YES" 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
rngDest1.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
End If
End Sub

And this also seems to be working OK.

The final touch I want to add is an autosort on Column B after a move on either sheet. Ie If I move from Sheet 2 to Sheet 1 - I want to autosort sheet 1 and If I move from Sheet 1 to Sheet 2 I want to auto sort on sheet 2.

I suspect I can add an autosort function between

End If
End Sub

but not really sure how I address the entire sheet and the changes to the ranges that happen with the autosort.

Thank-you for your help.

Darcy

 

[Legacy 347579]

Sorry - posted Error in the code for the second page and can not see how to exit my previous post Code should be

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest2 As Range
Set rngDest2 = Sheet1.Range("rngDest2")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Sheet2.Range("rngTrigger2")) Is Nothing Then
' Only trigger if the value entred is a date or is recognizable as a valid date
If UCase(Target) = "NO" 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
rngDest2.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
End If
End Sub

Darcy

 

[BigC]

Hi Darcy & welcome to the Forum

Yes, you can add code to sort your data after the move code in your existing procedure has done its work.

To do so I would suggest you create a Defined Name that DYNAMICALLY references the entire data set (i.e. the Name's dimensions need to expand and contract as rows are moved in and out - as should your trigger ranges in all sheets), as well as Names that reference the sort column/s. You can then assign these ranges to variables in the sort code, and perform the sort/s required. Either search the web for sort code, or use the Macro Recorder to capture the guts of what you want to achieve and then edit the resulting code to make it dynamic by declaring several range objects as variables and initialising them with your target Names.

In regard to your moving code, note that as you have only one destination on each, the Defined Name for the destination location on each sheet does NOT need to be different (e.g. rngDestn1, rngDestn2, etc.) By using the same name (i.e. rngDestn) and creating it with SHEET scope (versus Workbook scope), and then prefixing the initialisation of the variable in your code with the relevant Sheet name (e.g. Set rngDest = Sheet1.Range("rngDest") ) you reduce the customisation in each procedure. In essence, the only thing that changes is the source sheet and the target sheet (as the former - whichever sheet - has the trigger range and the latter - whichever sheet - has the destination range)

HTH