Moving rows between Excel worksheets using VBA

I have created a worksheet to reflect project status. One of the columns is, surprisingly, "Project Status", which calls from a list of values. One of these values is "Completed".
I have two relevant sheets in the workbook, both with identical column structure: one to reflect projects In Flight, and the second to show Completed projects, so my objective is to implement code which would validate the project status cell (column R), and where the value is "Completed" the entire row will be copied to the Completed sheet and then deleted from the In Flight sheet.
I would also need a reverse solution to allow for human error. So in case the status was updated in error and the row moved, the user could then access the "Completed" tab, change the status back to something else other than "Completed" and then the row would be moved back to the In Flight sheet.
The row to be occupied, in either direction would be the next available empty row.
Hope that makes sense to someone...!

I have tried this, and it appears to do more-or-less what I am trying to achieve, but then Excel freezes! I think it is going into a loop somewhere but cannot locate it.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Set i = Sheets("InFlight")
    Set e = Sheets("Completed")
    Dim d
    Dim j
    d = 4
    j = 5
    
    Do Until IsEmpty(i.Range("R" & j))
    
    If i.Range("R" & j) = "Completed" Then
        d = d + 1
        e.Rows(d).Value = i.Rows(j).Value
        i.Rows(j).EntireRow.Delete
    End If
    j = j + 1
    Loop
End Sub

Open in new window

Paul RichardsConsulting Implementation ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
It really makes more sense to keep all the data in one sheet, then you can use AutoFilter to quickly see various items according to Status
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please give this a try...

1. Code on Data Sheet Module: Assuming the Main Sheet is called Data. if not, change it in line#4 of the last two codes. Remember that this code will only be triggered once you change the status in column R.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim dws As Worksheet
Dim MoveRow As Boolean
On Error GoTo Skip
If Target.Column = 18 And Target.Row > 1 Then
    Application.EnableEvents = False
    Select Case Target.Value
        Case "InFlight"
            Set dws = Sheets("InFlight")
            MoveRow = True
        Case "Completed"
            Set dws = Sheets("Completed")
            MoveRow = True
    End Select
    If MoveRow Then
        Target.EntireRow.Copy dws.Range("A" & Rows.Count).End(3)(2)
        Target.EntireRow.Delete
    End If
End If
Skip:
Application.EnableEvents = True
End Sub

Open in new window


2. Code on InFlight Sheet Module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim dws As Worksheet
Set dws = Sheets("Data")    'The Sheet with Source Data where this Row will be copied back after changing the status back
On Error GoTo Skip
If Target.Column = 18 And Target.Row > 1 Then
    If Target.Value <> "InFlight" And Target.Value <> "Completed" Then
        Application.EnableEvents = False
        Target.EntireRow.Copy dws.Range("A" & Rows.Count).End(3)(2)
        Target.EntireRow.Delete
    End If
End If
Skip:
Application.EnableEvents = True
End Sub

Open in new window


3. Code on Completed Sheet Module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim dws As Worksheet
Set dws = Sheets("Data")    'The Sheet with Source Data where this Row will be copied back after changing the status back
On Error GoTo Skip
If Target.Column = 18 And Target.Row > 1 Then
    If Target.Value <> "InFlight" And Target.Value <> "Completed" Then
        Application.EnableEvents = False
        Target.EntireRow.Copy dws.Range("A" & Rows.Count).End(3)(2)
        Target.EntireRow.Delete
    End If
End If
Skip:
Application.EnableEvents = True
End Sub

Open in new window

Bill PrewIT / Software Engineering ConsultantCommented:
It really makes more sense to keep all the data in one sheet, then you can use AutoFilter to quickly see various items according to Status

It may not make sense yet, or maybe not the way you are thinking about it, but I agree with Roy here.  Anytime you can keep data in one place for future usage / reporting instead of two or more place, that's a win.  Having data on a single sheet, you can filter as Roy mentioned to just focus on one status or another, or you can sort by status column and group all the active separate from closed, etc.  Unless you are working with many thousands of projects it feels like the right approach to me.  

Things can go wrong when moving data between sheets, it will take more effort to make that perfect.

And what about when you have to field a question like, hey "Paul", what state is project XYZ in?"  Do you really want to have to search through multiple sheets to answer the question?  A lot easier to look at the master data sheet and answer that question.  You can always build additional reporting / query sheets from particular status or other attributes, but I would let them be just that, reports against the single source of data.

It also makes pivot sheets off the data easier down the road, when you need to report on all projects, not just one status...

»bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
Paul RichardsConsulting Implementation ManagerAuthor Commented:
Thank you all for your quick responses! I will leave one master sheet & create reports etc from that.
Thanks again.
Bill PrewIT / Software Engineering ConsultantCommented:
Paul,

With your permission I'd like to re-open this question and suggest you split points between Roy's first post and mine that you selected.  He really does deserve some recognition since he mentioned this first...

»bp
Roy CoxGroup Finance ManagerCommented:
I started to post this draft from a web page that I'm working on last night but didn'y get chance to finish.

Data Layout in Microsoft Excel.
Microsoft Excel can be used as a Database and if you take a close look you will find specific Database Functions such as DGet, Dsum etc. Add to this PivotTables, the SubTotal Function, Sumif , CountIf etc and you can build quite powerful databases in Excel. In addition, the later versions have the powerful Table feature. So, when you're ready to keep track of contacts, sales, expenditures, or whatever it is you're using Excel for, you will find plenty of tools to work with. However, to use these tools the data must be laid out correctly so that it organises what you're tracking in such a way that you can easily manipulate the data, create formulas to work with the data, or even export it to various formats and link it into existing applications (i.e., Database Applications). Below are some guidelines to follow to help keep your data nice, neat, and easy to work with:
1.      Keep your data in one continuous block. This means that you've got field names across the top row, and data starting directly underneath (row 2).
2.      Don't skip any rows to "break up" the data (you can easily use colours instead), and don't leave blank or unnecessary columns cluttering up your sheet. If you don't use it, get rid of it.
Titles/Headers
It's best to use a header row and a good idea is leave clear the top two or three rows above the header, and then keep your data underneath.
Keep your data formats right.
Never use apostrophes in front of numbers or text. Using the apostrophe tells Excel to store whatever it is you've typed as text, and why would you want numbers stored as text? You don't. If you need leading zeros, you can use a custom format to accomplish it. If you need to sort numbers stored as text, they will sort incorrectly. By following those these simple rules, you'll be on the fast track to easy data analysis and manipulation!
Paul RichardsConsulting Implementation ManagerAuthor Commented:
Roy thank you very much for this information!
Bill I am new to this game, so no idea how to do that! Looked for a REOPEN function on the site but could not locate this. If I can ask for some direction, I will be happy to split the points.
Roy CoxGroup Finance ManagerCommented:
There's an option to request assistance, see this
Paul RichardsConsulting Implementation ManagerAuthor Commented:
Thank you gentlemen for your assistance, both with the original question and the allocation of points!
Hope I did this right now.
Bill PrewIT / Software Engineering ConsultantCommented:
Thanks for re-closing the question Paul, I think this works better.

»bp
Roy CoxGroup Finance ManagerCommented:
Here's an example of how I would build a report from data stored in one sheet
AdvancedFilter-REPORTING-.xlsm
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.