Avatar of Paul Richards
Paul Richards

asked on 

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

VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Roy Cox
SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
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

ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Paul Richards
Paul Richards

ASKER

Thank you all for your quick responses! I will leave one master sheet & create reports etc from that.
Thanks again.
Avatar of Bill Prew
Bill Prew

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
SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Paul Richards
Paul Richards

ASKER

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.
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

There's an option to request assistance, see this
Avatar of Paul Richards
Paul Richards

ASKER

Thank you gentlemen for your assistance, both with the original question and the allocation of points!
Hope I did this right now.
Avatar of Bill Prew
Bill Prew

Thanks for re-closing the question Paul, I think this works better.


»bp
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Here's an example of how I would build a report from data stored in one sheet
AdvancedFilter-REPORTING-.xlsm
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo