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.

 

[fatang1664]

Ok thank you for you help. I don't quite know what is going here as I have resolved this issue already and had created a new post but for some reason this one started up again. Really sorry for wasting anyone's time.

I will try to cancel it.

I don't really think the excel website could be updated to be a bit more user friendly.

Cheers

 

[bradellin]

Going to dredge this up again. I got what accomplished what I originally set out to do, thank you everyone who helped. Now, related to this topic, once I copy the row I want to delete the contents of one cell and one cell only. Do I need a new macro/VBA code or tack it onto this one? Or, is this a question better suited to it's own thread?

 

[tthering]

Hi all,

I am trying to have a number greater than 0 to automatically move the row into an outperformance section and then a number less than 0 move into an underperformance section on the same worksheet. I have rngDest set to the row right under outperformance and then rngDest2 set to right under underperformance but I cannot get the trigger to work.

Code below:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngTrigger As Range
Dim rngDest As Range
Dim rngDest2 As Range2
Set rngTrigger = Sheet1.Range("rngTrigger")
Set rngDest = Sheet2.Range("rngDest")
Set rngDest2 = Sheet2.Range2("rngDest")
If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entered is a number or is recognizable as a number
If The number(Target) > 0 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
' Only trigger if the value entered is a number or is recognizable as a number
If The number(Target) < 0 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

Any help?

 

[tthering]

Never mind. I figured it out!

 

[bql2016]

This caters for the use of dates to signal that an Open item is to be Closed (I don't know how to capture the Checkbox scenario)

I have assumed that the sheets in your workbook are actually named "Worksheet 1" and "Worksheet 2" - otherwise you'll need to change the 3rd line of code below to suit.

1. Assign a Defined Name to the entire last column, or just the range of cells, on Worksheet 1 in which completed dates are to be entered as "rngTrigger" (that's "r" "n" "g" ... not "m" "g")
2. On Worksheet 2, select the entire row immediately under the last entry and assign the Defined Name "rngDest" to it (newly closed rows will be inserted above this location).
3. Paste the following code into the Worksheet object for Worksheet 1 (not in a Module) in the Visual Basic Editor (select the tab for this sheet, then right click and select "View Code")

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Worksheets("Worksheet 1").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 entred 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

Note that this will not work properly if you copy a completed date and simultaneously paste it to a range of cells in Worksheet 1, so you need to tag items in Worksheet 1 one at a time.

Let me know how it goes.

This was really useful, thank you BigC!
My worksheets have extra columns with different functions calculating reminders according to start and end dates.
Is it possible to code in order to do this: when you move a row from Worksheet 1 to Worksheet 2, I need the row to submit to the functions in Worksheet 2, and then if you move the row back to Worksheet 1, the row to submit back to the functions in Worksheet 1?
Is that achievable?
Thanks to your code, I`ve managed to move rows from one sheet to another, but when I do it, the rows are still submitting to the functions they had in sheet 1, which messes up the role of worksheet 2.
Many thanks!

 

[BigC]

My worksheets have extra columns with different functions calculating reminders according to start and end dates.
Is it possible to code in order to do this: when you move a row from Worksheet 1 to Worksheet 2, I need the row to submit to the functions in Worksheet 2, and then if you move the row back to Worksheet 1, the row to submit back to the functions in Worksheet 1?

It's difficult to answer your question without knowing more about the functions/formulas you're using on each sheet, so it would be helpful if you could post some detail about each and how they're used in each row. Having said that, I don't see why your objective cannot be met - though it may require some modifications to your workbook.

The general guide I would offer is that you need to try to have each row to stand on its own (i.e. referring to other cells in the row is ok but not directly to any cells outside the row) so that when it is moved, the same relative referencing still applies. This becomes tricky if you need to reference a single cell value on the sheet that is referenced by all rows (e.g. a master variable), but there are ways to get around this.

Cheers

 

[bql2016]

It's difficult to answer your question without knowing more about the functions/formulas you're using on each sheet, so it would be helpful if you could post some detail about each and how they're used in each row. Having said that, I don't see why your objective cannot be met - though it may require some modifications to your workbook.

The general guide I would offer is that you need to try to have each row to stand on its own (i.e. referring to other cells in the row is ok but not directly to any cells outside the row) so that when it is moved, the same relative referencing still applies. This becomes tricky if you need to reference a single cell value on the sheet that is referenced by all rows (e.g. a master variable), but there are ways to get around this.

Cheers

Hi,BigC!
Thanks for the prompt response!
Worksheet 1 is for current deals, Worksheet 2 for lost deals.
Worksheet 1 has start date column, end date column, reminder after 90 days of start date column, outcome of contact column, reminder 6 months before end date column and renewal (Y/N/Pending) column. Reminder columns have nested ifs (if blank than nothing, if text in outcome column then "contacted", etc.).
Worksheet 2 has only end date(date of loss) column, 90 days after losing reminder column, outcome of contact column, 1 year after losing reminder, and renewal (Y/N/Pending) column. Again, nested ifs are applied in the reminder columns (if blank than nothing, if text in outcome c. then "contacted", etc.)
Now, with the code you wrote at the beginning of the thread I have managed to make rows move from one worksheet to another. But it was only then I realized that what I did made no sense, because obviously the 2 worksheets calculate different stuff.
The idea is to move a row(deal) from existing to lost if the deal is lost. Once moved to Lost worksheet, I need it to calculate the nested ifs from worksheet 2 (work that is based strictly on the end date).
That means I`m left with a column as starting date in worksheet 2 that has no use to me.
Yet again, let`s say that meanwhile one of your lost deals becomes a current deal again. I would like (once I write "Y"in the Renewal column in Wsheet 2) to automatically move back to Current deals worksheet and that worksheet to calculate the nested ifs from wsheet 1 ( and to include the starting date as well, considering that one function calculates according to that date).
Does that make any sense?

 

[BigC]

Hi bql

Even with the info you've provided, it's still a little difficult to fully understand your application and devise a complete solution without having the workbook at hand, but we may be able to point you in the right direction.

As mentioned in my previous post, you really need each row to stand on its own as a self-contained unit so that it can be moved from one sheet to another without retaining any links to the source sheet. However, what I didn't emphasize was that this autonomy needs to be effective on BOTH sheets! So here's an idea/solution to consider.

Each worksheet needs to have all columns - both unique and common - required on both sheets, even if they don't serve a purpose on one of the sheets (i.e. the sheets are identical in layout). However, I don't think this should be a problem - you should be able to simply hide these columns on the sheet where they're not needed; any formulas will still calculate but you just won't see the results.

Now, from the info you've provided I think this would mean (but you being more familiar will need to double-check my assessment) that each row on both sheets will need the following columns (arrange in the order that makes sense to you):

Start date, End date, Reminder#1 - 90 days after start date, Reminder#2 - 90 days after losing deal, Reminder#3 - 1 year after losing deal, Outcome of contact, Reminder#4 - 6 months before end date, Renewal (Y/N/Pending)
​

Does this sound workable?

 

[bql2016]

Hi bql

Even with the info you've provided, it's still a little difficult to fully understand your application and devise a complete solution without having the workbook at hand, but we may be able to point you in the right direction.

As mentioned in my previous post, you really need each row to stand on its own as a self-contained unit so that it can be moved from one sheet to another without retaining any links to the source sheet. However, what I didn't emphasize was that this autonomy needs to be effective on BOTH sheets! So here's an idea/solution to consider.

Each worksheet needs to have all columns - both unique and common - required on both sheets, even if they don't serve a purpose on one of the sheets (i.e. the sheets are identical in layout). However, I don't think this should be a problem - you should be able to simply hide these columns on the sheet where they're not needed; any formulas will still calculate but you just won't see the results.

Now, from the info you've provided I think this would mean (but you being more familiar will need to double-check my assessment) that each row on both sheets will need the following columns (arrange in the order that makes sense to you):

Start date, End date, Reminder#1 - 90 days after start date, Reminder#2 - 90 days after losing deal, Reminder#3 - 1 year after losing deal, Outcome of contact, Reminder#4 - 6 months before end date, Renewal (Y/N/Pending)
​

Does this sound workable?

This is a great idea, BigC! Always so helpful!
Thank you very much!

 

[BigC]

bql

No worries - that's what this Forum is all about.

Please post back to advise whether this idea is successful or not.