Moving "completed" rows to another sheet

Kbkr50

New Member
Joined
Dec 14, 2016
Messages
5
Hello,

I will be using two sheets - both identically labeled, spaced, etc. Sheet 1 (Name is "Open Items") will consist of the input data, with it being columns A-R with various and simple formulas included (eg: Cell L120 =J120*H120) and also a Margin calculation and Profit calculation. Column R is going to be the data that I'd like to use to kick the entire row to the other sheet (Sheet 2 - named "Completed Items"). This will be a simple "YES" and "NO". The data will be generated onto "Open Items", with Column R stating "NO" until the task is complete (sometimes days, weeks, months) Once it is changed to "YES" I'd like the row to be moved to "Complete Items". If possible I would like this to be automatic from here on out - I have ~300 lines I need to move and I'd like to automate the process going forward as well as function properly for the existing rows. With the two sheets being identical, I'd like to have the simple formulas intact as it moves from "Open Items" to "Completed Items" with totals calculating a few rows from the bottom of the list and kept up to date as the data moves from "Open Items" to "Completed Items"

I know exactly what I want to happen, but no idea how to get it to work. I am terrible at explaining so please let me know if there is anything I can try to explain better or in more detail that would be of use.

Thank you very much for your time.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
My sincerest apologies. I searched through some posts that I thought would be close, but I wasn't able to find one that would fit for me.

Thank you for directly linking those. I will check them all out now.
 
Upvote 0
I'm sorry, none of these are working. I've changed all the data to match my sheet names and cells referenced but nothing's happening. I've added data validation for a "YES/NO" drop down but nothing moves to the other sheet.
 
Upvote 0
This would go into your "OPEN ITEMS" worksheet.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range
    Dim lastRow As Integer, lastRowOut As Integer, pasteRow As Integer
    Dim wsOpen As Worksheet, wsComplete As Worksheet
    
    Set wsOpen = ThisWorkbook.Sheets("OPEN ITEMS")
    Set wsComplete = ThisWorkbook.Sheets("COMPLETED ITEMS")
    
    lastRow = wsOpen.Cells(wsOpen.Rows.Count, 2).End(xlUp).Row

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = wsOpen.Range("R2:R" & lastRow)
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

        For i = 2 To lastRow

            lastRowOut = wsComplete.Cells(wsComplete.Rows.Count, 2).End(xlUp).Row
            
            If lastRowOut = 1 Then
                pasteRow = 2
            Else
                pasteRow = wsComplete.Cells(wsComplete.Rows.Count, 2).End(xlUp).Row + 1
            End If
    
            If wsOpen.Range("R" & i).Value = "YES" Then
                
                wsOpen.Range("B" & i & ":R" & i).Cut Destination:=wsComplete.Range("B" & pasteRow)

            End If

        Next i
       
    End If
    
End Sub

Working example: https://1drv.ms/x/s!AnpELGec0aCql7lSO_iGnyGYehvlrA

Take a look, modify to suite your needs. Post back if you need help.
 
Last edited:
Upvote 0
@Kbkr50

You've run out of room in your PM box and I am unable to reply. Please delete some older messages.

Kbkr50 said:
it seems to move every row to the "completed items" sheet down to row 107 on the "open items" sheet, then pops up the message above.

I have tested it by changing 110 rows to "YES" (random ones throughout the list; in no particular order). I'm not getting any error message and it successfully pastes items beyond row 107 on the completed sheet.

Please clarify what you are seeing:

1) if you choose "YES" for just one of the rows, it moves every single row instead of just that row?

2) or, that it works up until the completed items reach row 107 of the completed sheet, at which point it starts throwing errors each time you try and move a completed item?
 
Upvote 0
I believe I have it fixed. I'm not sure what caused it - but since I had multiple rows already marked "yes", when I changed one it threw them to the completed sheet all at once and hiccuped half way through. I changed all to "no" on the open items sheet and individually marked them "yes" as I did so each row moved over to the other sheet! so it's working perfectly.

I'm not sure if there's a way to do this (and honestly it's no hassle at all the way it is) but the only improvement on this would be to shift the rows of "no" items on the open items sheet up after the row above it is completed and moved to the completed sheet. As of right now it moves the data to the completed items sheet but the row still keeps its place even though the data has moved. No big deal at all, though. Easy fix.

Thanks again

Thank you so much for your time and effort to help me with this, going forward this is going to be great.
 
Upvote 0
I'm not sure if there's a way to do this (and honestly it's no hassle at all the way it is) but the only improvement on this would be to shift the rows of "no" items on the open items sheet up after the row above it is completed and moved to the completed sheet.

Add the code highlighted in blue:

Rich (BB code):
If wsOpen.Range("R" & i).Value = "YES" Then

	wsOpen.Range("B" & i & ":R" & i).Cut Destination:=wsComplete.Range("B" & pasteRow)
	wsOpen.Rows(i).EntireRow.Delete

End If

Thank you so much for your time and effort to help me with this, going forward this is going to be great.

You are welcome.

Merry Christmas.
-B
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top