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

joeyjay · Jan 3, 2012

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.

zoraxe · May 18, 2013

BigC,

How can I (you) modify the code in order the cut rows to be transferred in a different workbook (e.g. Book 2)?

The approach with the same workbook but different sheet is building me a massive archive which is slowing down the current work. If the rows could be transferred to a new workbook, we would still have the working workbook and other workbook that would be used as storage.

I tried something, but unfortunately couldn't it by myself

Thanks a lot for your help and advice!

fatang1664 · Nov 21, 2013

Hi,

Can you help.

I am trying to do something simular but I only want to transfer the whole row only when a certain year is put in the due date cell and not just any date. Also will the VB transfer all the formulas?

[TABLE="width: 1667"]
<TBODY>[TR]
[TD]Customer / Project[/TD]
[TD][/TD]
[TD][/TD]
[TD]2012 Actual[/TD]
[TD]Org Chart[/TD]
[TD]Due Date[/TD]
[TD]Potential[/TD]
[TD]Grade[/TD]
[TD]Stage[/TD]
[TD]Forecast[/TD]
[TD][/TD]
[TD]% Chance[/TD]
[TD]MH Forcast[/TD]
[TD]Notes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project2[/TD]
[TD]Bitmap Bitmap [/TD]
[TD]Bitmap Bitmap [/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]£15,000[/TD]
[TD]3[/TD]
[TD]F[/TD]
[TD]£15,000[/TD]
[TD]l[/TD]
[TD]50%[/TD]
[TD]£15,000[/TD]
[TD="colspan: 4"]Bla Bla Bla[/TD]
[/TR]
[TR]
[TD]Project3[/TD]
[TD]Bitmap Bitmap [/TD]
[TD]Bitmap Bitmap [/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]£15,000[/TD]
[TD]3[/TD]
[TD]F[/TD]
[TD]£15,000[/TD]
[TD]l[/TD]
[TD]100%[/TD]
[TD]£15,000[/TD]
[TD="colspan: 4"]Bla Bla Bla[/TD]
[/TR]
</TBODY>[/TABLE]

BigC · Nov 21, 2013

... but I only want to transfer the whole row only when a certain year is put in the due date cell and not just any date. Also will the VB transfer all the formulas?

Will the trigger 'year' be static or change from time to time? Either way, I would recommend that this value be entered into a separate cell somewhere in Sheet1, and be assigned a Defined Name (say 'ArchiveYear') so that it can be easily changed without amending the code.

The code which triggers the move is:

Code:

     If IsDate(Target) Then

so this just needs to be changed to fire when your condition is met. Assuming all other matters in your model are the same as the original, the procedure could then be something like this (note additional/amended lines):

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest as Range
Dim rngYear as Range
Set rngDest = Sheet2.Range("rngDest")
Set rngYear = Sheet1.Range("ArchiveYear")

' Limit the trap area to range of cells in which dates are entered as defined above
If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then
' Only trigger if the year of the value entered matches the nominated archive year entered in the Defined Name "ArchiveYear"
     If Year(Target) = rngYear 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

If I recall correctly (it's been a while and I don't have a copy of the model with me at the moment) the formulae in the model for which the original code was created only referred to other cells in the same row, so cutting and pasting them to another location does not cause a problem. However, if there are formulae that refer to cells outside that same row then it's another ball game.

HTH

fatang1664 · Nov 24, 2013

Thanks BigC,

That code worked great.

However, another thought cross my mind that on some instances the date maybe brought back a year back to the orignal sheet 1 or 2. I've played around abit with the code and managed to send the cells forward to sheet 1 for 2014 and sheet 2 for 2015 projects but can't see how I can revert them back if the date was changed as the "rngTrigger" and "rngDest" has moved from its original location. I've tried to add "rngDest" 2 and 3 and "rngTrigger1" but no luck.

I'm not an expert with on VB so not familiar with the syntax. Can you help?

Regards

Chris

BigC · Nov 27, 2013

Hi Chris
Sorry, I don't understand what you're trying to do. What do you mean by:

"the date maybe brought back a year back to the original sheet 1 or 2.", and
"send the cells forward to sheet 1 for 2014 and sheet 2 for 2015 projects but can't see how I can revert
them back if the date..."?

Can you please elaborate. (Note that the original application was not developed with any intention to allow for rows to be shuffled back and forth between "live" and "archive" sheets - but that doesn't mean we can't modify the original code to do so.)

fatang1664 · Nov 27, 2013

Sorry I hadn't made it clear.

What I need to do is the following.

I have a list of projects that I will put into sheet 1 and they will have an Due Date, however some may not start until 2014 or 2015 so they will need to be moved forward to sheets 2013 and 2014. In the future some of the projects in 2014 or 2015 due dates maybe brought forward to an earlier year and therefore will need to be moved back into 2014 or 2013.

Example: Sheet 2013 - all 2014 project will move to Sheet 2014 but may need to be moved back to sheet 2013 if the project is brought forward etc.

[TABLE="width: 1303"]
<TBODY>[TR]
[TD][/TD]
[TD]Customer / Project
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Org Chart
[/TD]
[TD]Due Date
[/TD]
[TD]Potential
[/TD]
[TD]Grade
[/TD]
[TD]Stage
[/TD]
[TD]Forecast
[/TD]
[TD][/TD]
[TD]% Chance
[/TD]
[TD]MH Forcast
[/TD]
[TD="colspan: 4"]Notes
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30 Old Bailey (Skanska)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]1 January 2014
[/TD]
[TD]£99,000
[/TD]
[TD]1
[/TD]
[TD]C
[/TD]
[TD]£0
[/TD]
[TD]l
[/TD]
[TD]25%
[/TD]
[TD]£0
[/TD]
[TD="colspan: 4"]Blaa Blaa
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]31 Old Bailey (Skanska)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]1 February 2013
[/TD]
[TD]£99,001
[/TD]
[TD]2
[/TD]
[TD]C
[/TD]
[TD]£0
[/TD]
[TD]l
[/TD]
[TD]25%
[/TD]
[TD]£0
[/TD]
[TD="colspan: 4"]Blaa Blaa
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]32 Old Bailey (Skanska)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]1 February 2014
[/TD]
[TD]£99,002
[/TD]
[TD]3
[/TD]
[TD]C
[/TD]
[TD]£99,002
[/TD]
[TD]l
[/TD]
[TD]25%
[/TD]
[TD]£0
[/TD]
[TD="colspan: 4"]Blaa Blaa
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]33 Old Bailey (Skanska)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]12 April 2013
[/TD]
[TD]£99,003
[/TD]
[TD]4
[/TD]
[TD]C
[/TD]
[TD]£99,003
[/TD]
[TD]l
[/TD]
[TD]75%
[/TD]
[TD]£99,003
[/TD]
[TD="colspan: 4"]Blaa Blaa
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]34 Old Bailey (Skanska)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]12 July 2013
[/TD]
[TD]£99,004
[/TD]
[TD]5
[/TD]
[TD]C
[/TD]
[TD]£99,004
[/TD]
[TD]l
[/TD]
[TD]50%
[/TD]
[TD]£99,004
[/TD]
[TD="colspan: 4"]Blaa Blaa
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]35 Old Bailey (Skanska)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]23 June 2015
[/TD]
[TD]£99,005
[/TD]
[TD]6
[/TD]
[TD]C
[/TD]
[TD]£99,005
[/TD]
[TD]l
[/TD]
[TD]50%
[/TD]
[TD]£99,005
[/TD]
[TD="colspan: 4"]Blaa Blaa
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]36 Old Bailey (Skanska)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]1 January 2013
[/TD]
[TD]£99,006
[/TD]
[TD]7
[/TD]
[TD]C
[/TD]
[TD]£99,006
[/TD]
[TD]l
[/TD]
[TD]100%
[/TD]
[TD]£99,006
[/TD]
[TD="colspan: 4"]Blaa Blaa
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]37 Old Bailey (Skanska)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]1 February 2014
[/TD]
[TD]£99,007
[/TD]
[TD]8
[/TD]
[TD]C
[/TD]
[TD]£99,007
[/TD]
[TD]l
[/TD]
[TD]50%
[/TD]
[TD]£99,007
[/TD]
[TD="colspan: 4"]Blaa Blaa
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]38 Old Bailey (Skanska)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]£99,008
[/TD]
[TD]9
[/TD]
[TD]C
[/TD]
[TD]£99,008
[/TD]
[TD]l
[/TD]
[TD]50%
[/TD]
[TD]£99,008
[/TD]
[TD="colspan: 4"]Blaa Blaa
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]39 Old Bailey (Skanska)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]£99,009
[/TD]
[TD]10
[/TD]
[TD]C
[/TD]
[TD]£99,009
[/TD]
[TD]l
[/TD]
[TD]50%
[/TD]
[TD]£99,009
[/TD]
[TD="colspan: 4"]Blaa Blaa
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]40 Old Bailey (Skanska)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]£99,010
[/TD]
[TD]11
[/TD]
[TD]C
[/TD]
[TD]£99,010
[/TD]
[TD]l
[/TD]
[TD]50%
[/TD]
[TD]£99,010
[/TD]
[TD="colspan: 4"]Blaa Blaa
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]41 Old Bailey (Skanska)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]£99,011
[/TD]
[TD]12
[/TD]
[TD]C
[/TD]
[TD]£99,011
[/TD]
[TD]l
[/TD]
[TD]50%
[/TD]
[TD]£99,011
[/TD]
[TD="colspan: 4"]Blaa Blaa
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]42 Old Bailey (Skanska)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]£99,012
[/TD]
[TD]13
[/TD]
[TD]C
[/TD]
[TD]£99,012
[/TD]
[TD]l
[/TD]
[TD]100%
[/TD]
[TD]£99,012
[/TD]
[TD="colspan: 4"]Blaa Blaa
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]43 Old Bailey (Skanska)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]£99,013
[/TD]
[TD]14
[/TD]
[TD]C
[/TD]
[TD]£99,013
[/TD]
[TD]l
[/TD]
[TD]100%
[/TD]
[TD]£99,013
[/TD]
[TD="colspan: 4"]Blaa Blaa
[/TD]
[/TR]
</TBODY>[/TABLE]

(TAB 2013) (TAB 2014) (TAB 2015)

Thanks,

Chris

BigC · Nov 27, 2013

Hi Chris

If I understand you correctly, rather than rows being permanently removed from the single 'live' project sheet to a single 'archived projects' sheet for which the original application was developed, you want a set of live sheets (one per year - being the year in which project is due), with projects being moved between sheets as and when necessary according to a change in the year of the due date.

Questions:

Is my understanding, as above, correct?
What will be the trigger for a move - a change in the year of the project's due date , or something else?
Do completed projects stay on their respective year sheet of are they to be moved to an archive sheet?

If your desired outcome is as I've outlined above, this should not be too difficult to achieve. Basically, each sheet would have identical code which would assess whether there is a change in year for any project on that sheet, and if so, move that row to the relevant sheet. (You may need to use Data Validation on the Due Date field of each sheet to ensure that the only dates accepted are those for which there is a year sheet in the workbook.)

fatang1664 · Nov 28, 2013

Hi,

I can confirm that the answer to your questions 1&2 is yes and again for Q3 I would like to archive projects if the projects trigger is Lost or Complete.

PS What the weather like over there?

Regards

Chris.

BigC · Nov 28, 2013

Hi Chris

The weather in Perth is good (depending on what one wishes to do of course!) - though we're still experiencing generally dry conditions.

Re archiving projects if the projects trigger is Lost or Complete: What field in your table contains this trigger?

It will be difficult to write the code and provide instructions to modify the workbook to suit, so I'll send you a Private Message from within this site with my email address so you send me a copy of your file. I think I have the workbook I developed for the original post at home so can copy the code from there and modify it to suit.

ansmith · Dec 6, 2013

I have the same application as zoraxe, but I can't get the macro to work. I am a novice user and I am probably doing something incorrect. Below is what I have, Sheet1 is my active list and sheet2 is my archive. I am trying to move an entire row when column P is changed to "closed". Do I have to Define a name "closed"? on both sheet1 and shee2? I can't get the macro to run and when I step through it I have a bug on the row:
If Not Intersect(Target, Sheet1.Range("Closed")) Is Nothing Then

Macro:
Sub Worksheet_Change()
Dim rngDest As Range
Dim rngYear As Range
Set rngDest = Sheet2.Range("Closed")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Sheet1.Range("Closed")) Is Nothing Then
' Only trigger if the value entred is a date or is recognizable as a valid date
If UCase(Target) = "closed" 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

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

joeyjay

New Member

zoraxe

New Member

Excel Facts

fatang1664

New Member

BigC

Well-known Member

fatang1664

New Member

BigC

Well-known Member

fatang1664

New Member

BigC

Well-known Member

fatang1664

New Member

BigC

Well-known Member

ansmith

New Member

Similar threads

Forum statistics

Share this page

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

New Member

New Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock