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.

 

[BigC]

This may not be the cause of your problem, but is a distinct possibility.

The use of "ClosedItems" in the following line:

Set rngDest = ClosedItems.Range("rngDest")
​

does NOT refer to the tab name you see when using Excel spreadsheet interface, but to the VBA CODE NAME of the sheet object in the VBA project list under Microsoft Excel Objects. The code name provides a more direct means of referencing a sheet via VBA.
For this code to work you need to change (in the Properties window at bottom left of VBE screen) the code name of the sheet object from, say "Sheet2" to ClosedItems". Once done, both the tab name and code name will be the same.

Follow my instructions exactly re defining ranges (including having a header row, a "fence" row as the bottom extremity, and use of formulae to dynamically resize the range to accommodate the number of value rows) and you shouldn't go wrong. Most previous users have had trouble when they tried to do their own thing without really understanding how that impacted the VBA code.

 

[bhamm]

For this code to work you need to change (in the Properties window at bottom left of VBE screen) the code name of the sheet object from, say "Sheet2" to ClosedItems". Once done, both the tab name and code name will be the same.

That was it, exactly. Thanks!

Follow my instructions exactly re defining ranges (including having a header row, a "fence" row as the bottom extremity, and use of formulae to dynamically resize the range to accommodate the number of value rows) and you shouldn't go wrong. Most previous users have had trouble when they tried to do their own thing without really understanding how that impacted the VBA code.

Pardon my ignorance. I think I have the first 2 down, how can I use formulae to dynamically accommodate the number of value rows?

Otherwise, awesome, thanks!!!

 

[BigC]

Search Google for "excel dynamic ranges". Essentially, use the Offset function in conjunction with Count or Counta function (or other alternatives that can determine the number of rows or columns in the variable sized range)
in case you didn't know, Named Ranges are really just formulae with a name, which is why the RefersTo value always starts with an equal sign (=)

 

[bhamm]

Many many thanks BigC! I will look into that

 

[BigAaron]

Hi I was wondering if anyone could help me. I am trying to move entire rows from several sheets onto a new sheet(within the same workbook) but only if they have the correct date. Column a-e is just filled with data, but on column f the cells are dates. Is it possible to copy the records that are in a specific date range and paste them into a new sheet. The aim to have all of the records from the different sheets in a consolidated new sheet ranked by date. Many thanks, Aaron

 

[Lerincho]

BigC,

Amazing work. For the most part I have been able to similar code to work. There is only one hiccup that I have found. Similarly I am doing a move from a master listed of pending projects sheet to a completed sheet. The hiccup centers around that it is not moving the code to the last line of the completed sheet, instead it posts is exactly in the middle (row 875 specifically). I know I am missing something small that will make me feel a fool, but needing help. Undoubtedly something is wrong with my destination code, but for the life of me cannot figure out what is missing.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng1 As Range
    Dim rng2 As Range
    Set rng1 = Target.EntireRow
    Set rng2 = Worksheets("Completed").Cells(Rows.Count, 1).End(xlUp) _
               .Offset(1, 0)
    If Target.Column = 13 Then
        On Error GoTo endit
        Application.EnableEvents = False
        If Target.Value = "Complete" Then
            With rng1
                .Copy Destination:=rng2
                .Delete
            End With
        End If
    End If
endit:
    Application.EnableEvents = True
End Sub

 

[BigC]

Hi Lerincho & welcome to the Board

It sounds like the following code line is not finding the correct position:

Set rng2 = Worksheets("Completed").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

To check this, do some debugging of your code:

1. Insert a temporary line with the following, immediately after the "Set rng2 ..." line:

   rng2.Select

2. Toggle on a Breakpoint (Under Debug on VBA menu) on the next line in your code to halt the macro execution at that point:

   If Target.Column = 13 Then

3. Enter a value into the sheet that will trigger the code
4. See what range has been selected by the insert code.
5. Reset macro to break/stop it at that point.

I'm always a bit nervous of using a dynamic means of locating the insert point [e.g. Worksheets("Completed").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)] , hence why I elected to use a "fixed" table structure rather than fluid Excel Tables, and created a Defined Name ("rngDest") for a known fixed point for the destination/insert location (the cell in column A of the "fence" row which delineates the bottom boundary of my tables).

If you need to dig further:

1. toggle a Breakpoint on "Set rng1 = Target.EntireRow"
2. enter a value into the sheet that will trigger the code
3. go back to the VBE and press F8 to step through each line one at a time, but also mousing over individual components of each line to see what value they are returning (e.g. once "Set rng2 = Worksheets("Completed").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)" has been executed, mouse over Rows.Count to see what value this statement returns. Alternatively, break these components into separate lines of code and use a MessageBox to report the value to screen.

 

[Lerincho]

Thank you for the response. Trying to get this to work while in the middle of various of the projects does have me slightly limited in in that unable to take any type of refresher course on VB.

You are correct it is having a problem with that line. The temporary code is returning error 1004. If I wanted to adjust the code to make it a fixed point, would I be taking the the

Set rngDest = Worksheets("Complete").Range("rngDest")

and adjusting the

.Copy Destination:=rng2]

to your

rngDest.Insert Shift:=xlDown

Thank you again, this thread has been an absolute asset.

 

[BigC]

That's it - you don't want to destroy the destination, and always want the copied/moved rows to be above the bottom "fence" of the destination table/sheet (at least in the applications that follow my methodology, for the fence also acts as a permanent location to which the formulae used in the RefersTo definition of other Defined Names hook onto. In this way, any additions or deletions of data rows always occur inside the boundaries of each table.)

 

[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