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]

No worries Bob. I'm glad you managed to quickly adapt your application to suit the code (rather than the other way around which would have taken a while!). Do to the nature of this application, the set up is a bit tricky for anyone without reasonable Excel experience/skills, with trigger & destination ranges, and Defined Names (with Sheet scope). But it's not magic - just logic (computers will only do what we tell them to do, and in doing so follow those instructions precisely!!)

Cheers

 

[BigC]

For those interested, here is some code to deal with the situation where the data is held in Excel (structured) Tables rather than simple spreadsheet ranges.

The workbook for which this code was written has the following:

• Sheet1 (VBA code name) named "Open Items" containing an Excel Table named "tblOpenItems" that holds the original data (a log of open issues), a key field of which is named "Closed Date"
• A named range called "RngTrigger" has been created to reference the data rows of this field. The name's RefersTo formula = tblOpenItems[Closed Date]. Note that this name has been set with Sheet scope rather than Workbook scope as this makes the code easier to replicate if your application moves data to/from a number of sheets rather than just from one to another as in this example, as you can have a "RngTrigger" on each sheet)
• Sheet2 (VBA code name) named: "Closed Items" containing an Excel Table named "tblClosedItems"

The process requires the row of data in the tblOpenItems table to be 'moved' to the tblClosedItems table when a valid date is entered in the "Closed Date" field of tblOpenItems. The 'move' is achieved by copying the target data from the source Table, pasting it into the destination Table, then deleting the row from the source Table. (There may be a better way to do this but I couldn't find one! )

The following code is located behind the "Open Items" sheet (i.e. in the VBE code window of the Sheet1 object):

Private Sub Worksheet_Change(ByVal Target As Range)

' Purpose: Automatically move the data in a row from an Excel Table on one sheet to another Excel Table on another sheet.
'               The 'move' is achieved by copying the target data from the source Table, pasting it into the destination Table, then deleting the row from the source Table
    
    Dim tblSrc As ListObject
    Dim tblDest As ListObject
    Dim RngTrigger As Range
    Dim RngSrc As Range
    Dim DestRow As ListRow
    Dim HasDV As Integer
    
    'Initialize variables with object names ( >> MUST match names defined by User in Excel << )
    Set tblSrc = Sheet1.ListObjects("tblOpenItems")
    Set tblDest = Sheet2.ListObjects("tblClosedItems")
    Set RngTrigger = Sheet1.Range("rngTrigger")         'NOTE: Defined Name has scope = sheet rather than Workbook
    

    ' Limit the trap area to range of cells in which completed dates are entered as defined above
    If Not Intersect(Target, RngTrigger) Is Nothing Then

        ' Only trigger if the value entred is a date or is recognizable as a valid date
         If Target.Rows.Count = 1 And IsDate(Target) And Target.Value <> "" 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
                
            'Copy target row
            Set RngSrc = Intersect(ActiveCell.EntireRow, tblSrc.DataBodyRange)
            Set DestRow = tblDest.ListRows.Add

            RngSrc.Copy
            
            With DestRow.Range
            .PasteSpecial xlPasteFormats            '>>> Uncomment if this type of paste is required <<<
            '.PasteSpecial xlPasteValues            '>>> Uncomment if this type of paste is required <<<
            .PasteSpecial xlPasteFormulas           '>>> Uncomment if this type of paste is required <<<
            End With
            
            'Check pasted range for Data Validation and clear if exists
            On Error Resume Next
                HasDV = DestRow.Validation.Type
                If Err.Number = 0 Then DestRow.Range.Validation.Delete
            On Error GoTo 0
            
            Application.CutCopyMode = False
            
            'Delete source row
            Selection.Delete
                
            ' Reset EnableEvents
            Application.EnableEvents = True
        
        End If
    
    End If

End Sub

This application is a little simpler than that which spurred the creation of this thread and the number of variations subsequently generated because it requires only one Defined Name / named range in the source sheet / table (i.e. there no need for a Defined Name to identify the destination point). It also 'moves' a range of data rather than an entire row.

I'm sure someone with more Excel & VBA skill than me can achieve the same result with a more elegant, efficient and robust design, but this is the best I can do at this point.

I hope it proves as useful as the previous applications proved to be.

Cheers

 

[bobsargent2002]

Big C,
GREAT information! I will give it a try!

Thanks,
Bob

 

[Infantryman]

I hope it proves as useful as the previous applications proved to be.

Cheers

BigC, Your help has been awesome in this thread. I was hoping to pick you brain a little more regarding the use of this with tables.

I thought I followed your code and changes as needed but I am getting a "Run-time error "91: Object variable or With Block variable not set."
When I De-Bug, it says the error is at the RngSrc.Copy code.

'Copy target row
Set RngSrc = Intersect(ActiveCell.EntireRow, tblSrc.DataBodyRange)
Set DestRow = tblDest.ListRows.Add


RngSrc.Copy

With DestRow.Range
.PasteSpecial xlPasteFormats '>>> Uncomment if this type of paste is required <<<'
.PasteSpecial xlPasteValues '>>> Uncomment if this type of paste is required <<<'
.PasteSpecial xlPasteFormulas '>>> Uncomment if this type of paste is required <<<'
End With

Your continued assistance with this is greatly appreciated.
To be honest, my task manager was working great with your guidance on the original post. Then I learned about creating a table to make task entry easier.
Since then, my tinkering has broke what was already working.

 

[Col Delane]

Hi Infantryman

It's a while since I wrote that code, so I'm a bit rusty, but by a process of deduction I think your problem is with the definition, or lack of, of several Excel objects required to make this code work (Object variable .... variable not set). Getting all these ducks named and lined up with the corresponding names in the code is key to the whole application.

As per the preamble to the code in my post #172 , the workbook for which that example code was written had the following setup - which you will need to either replicate exactly, or adapt the names used in the code to suit your setup:

1. A sheet object with a VBA code name of "Sheet1". This is NOT the sheet's tab name - read about this in line 2 below - but the object name BEFORE the parenthesis () displayed under the Microsoft Excel Objects hierarchy in the VBE Project window.
2. The tab name (what you see when looking at the spreadsheet) of this sheet is "Open Items". This also gets shown inside the parenthesis in the Microsoft Excel Objects hierarchy, as in "Sheet1(OpenItems)"
3. This sheet contains an Excel Table named "tblOpenItems" that holds the original data (a log of open issues), a key field of which is named "Closed Date"

Two of these objects are then "referenced" by the following code line which initializes/populates the "tblSrc" variable:

Set [B][COLOR=#ff0000]tblSrc [/COLOR][/B]= [COLOR=#0000ff][B]Sheet1[/B][/COLOR].ListObjects("[COLOR=#0000ff][B]tblOpenItems[/B][/COLOR]")

which is in turn referenced by the following code line which initializes/populates the "RngSrc" variable

Set [B][COLOR=#ff0000]RngSrc [/COLOR][/B]= Intersect(ActiveCell.EntireRow, [COLOR=#0000ff][B]tblSrc[/B][/COLOR].DataBodyRange)

So, double-check that you've got your objects named as above (or modify the code to match the names of your corresponding objects) . If this is not the problem then we'll have to have a deeper look.

I hope this helps.

 

[Infantryman]

Hello Col,
Thank you for replying and taking the time to dig through the archives to assist me.
I will admit that I am very new to trying to use VBA and working at this level of Excel, but I am eager to learn it and have enjoyed working with your block of code.

I have went through and double checked my defined names, and I believe they match up to how you described.

In sheet1(CurrentWork) there is "tblTaskOpen" with a scope of Workbook.

Table refers to =CurrentWork!$A$3:$H$14

In sheet2(CompletedWork) there is "tblTaskClosed" with a scope of Workbook.

Table refers to = CompletedWork!$A$2:$H$8

rngTrigger is set to a scope of CurrentWork

 Refers to  =tblTaskOpen[Completed]

*Completed being the column in the table

There may be an issue with the paste option I am using. Currently I am using all 3 options. I didn't know which one I should use.

I provided the entire code I am using below.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim tblSrc As ListObject
    Dim tblDest As ListObject
    Dim RngTrigger As Range
    Dim RngSrc As Object
    Dim DestRow As ListRow
    Dim HasDV As Integer
    
       Set tblSrc = Sheet1.ListObjects("tblTaskOpen")
    Set tblDest = Sheet2.ListObjects("tblTaskClosed")
    Set RngTrigger = Sheet1.Range("rngTrigger")         'NOTE: Defined Name has scope = sheet rather than Workbook
    

       If Not Intersect(Target, RngTrigger) Is Nothing Then

         If Target.Rows.Count = 1 And IsDate(Target) And Target.Value <> "" Then
                      
            Application.EnableEvents = False
                
            Set RngSrc = Intersect(ActiveCell.EntireRow, tblSrc.DataBodyRange)
            Set DestRow = tblDest.ListRows.Add


            RngSrc.Copy
            
            With DestRow.Range
            .PasteSpecial xlPasteFormats            '>>> Uncomment if this type of paste is required <<<'
            .PasteSpecial xlPasteValues            '>>> Uncomment if this type of paste is required <<<'
            .PasteSpecial xlPasteFormulas           '>>> Uncomment if this type of paste is required <<<'
            End With
            
            'Check pasted range for Data Validation and clear if exists
            On Error Resume Next
                HasDV = DestRow.Validation.Type
                If Err.Number = 0 Then DestRow.Range.Validation.Delete
            On Error GoTo 0
            
            Application.CutCopyMode = False
            
            'Delete source row
            Selection.Delete
                
            ' Reset EnableEvents
            Application.EnableEvents = True
        
        End If
    
    End If


End Sub

Thank you again for your effort and expertise.

 

[Col Delane]

Re the naming of objects (I still think this is the cause of your problem):

In sheet1(CurrentWork) there is "tblTaskOpen" with a scope of Workbook.

• Table refers to =CurrentWork!$A$3:$H$14

In sheet2(CompletedWork) there is "tblTaskClosed" with a scope of Workbook.

• Table refers to = CompletedWork!$A$2:$H$8

rngTrigger is set to a scope of CurrentWork

• Refers to =tblTaskOpen[Completed]

1. To match the way the code is written, "tblTaskOpen" and "tblTaskClosed" need to be the specific Excel object (i.e. animal) called an Excel Table (as created by selecting the spreadsheet range and pressing Ctrl+T to convert the range to a Table. If this is done you will see the Table Tools [Design] tab appear on your Ribbon when you select a cell anywhere inside the table boundary.
   
2. rngTrigger should be a Defined Name (created via Formulas / Name Manager)

Whilst related, these are two different types of objects even though both show up in the list of Names within Name Manager!

Set tblSrc = Sheet1.[COLOR=#0000ff]ListObjects[/COLOR]("tblTaskOpen")

is the code that initializes/populates the ListObject (i.e. Table) object variable named tblSrc with the EXCEL TABLE name "tblTaskOpen"​

Set tblDest = Sheet2.[COLOR=#0000ff]ListObjects[/COLOR]("tblTaskClosed")

is the code that initializes/populates the ListObject (i.e. Table) object variable named tblDest with the EXCEL TABLE name "tblTaskClosed"​

Set RngTrigger = Sheet1.[COLOR=#0000ff]Range[/COLOR]("rngTrigger")         'NOTE: Defined Name has scope = sheet rather than Workbook

is the code that initializes/populates the Range object variable named RngTrigger with the DEFINED NAME "rngTrigger"


Re Paste Options:

There may be an issue with the paste option I am using. Currently I am using all 3 options. I didn't know which one I should use.

This should NOT generate the error you reported in your first post. These lines offer a personal preference as to what you want to appear in the "archive" sheet. For example, if you retain the formulas once the row is copied to the CompletedWork sheet they may not work as they did in the CurrentWork sheet (depends on what else those CurrentWork formulas were referencing) - so you may need to convert the formulas to values to preserve the result at the time of copying. Do this by keeping the following line active

.PasteSpecial xlPasteValues

but delete or comment out (insert a single inverted comma ' before the period) this line:

.PasteSpecial xlPasteFormulas

I hope that helps. If not, post back with specifics about what is happening (or not), error messages, etc.

 

[cowboycraig]

I own a company and use Excel to track all of my calls and billing. 11 sheets: Road, Accounts, Calls, Dcalls, Est, Codes, Potential, Orders, BK, Payments, and Schedule. I have set up a column called "Move Code". I want to be able to create a code so that if I place an R in the Move Code column in the "Schedule" sheet, all the data for the that entire row automatically copies to the "Road" sheet. Using the same "Move Code" column, I would also like to be able to enter "C" in the "Road" sheet and have all the data copied to the "Calls" sheet.

 

[Col Delane]

Howdy Cowboy and welcome to the MrExcel Ranch! ?

What you're wanting is perfectly do-able with this application, modified as required - which means the VBA code behind the relevant sheet (i.e. Schedule for moves to Road and elsewhere, and Road for moves to Calls and elsewhere, etc.) is where you need to adapt the VBA code to test the entered Move Code against your criteria, and action accordingly.

Whilst the VBA code and setup for this application should still work, you will notice that it was developed a few years ago, before Power Query came along - so that latter functionality may be better suited for your purpose (I can't say as I missed the boat when Power Query set sail!)
If you decide to proceed with the VBA option, be sure to read all the posts that explain how to set up the application as there are some tricky bits (as I've pointed out on several occasions) with how you structure the sheets, and the appropriate Defined Names - with the formula used to define them, and the "scope" [Workbook or Sheet) applied to them - being critical pieces of the puzzle.

I'll endeavour to help when and if I can, but please bear in mind that it's two years since I looked at this post, and the Forum; demand on my time is quite different now; and my saddle is showing some signs of exposure to the elements! ?