Automatically Move Row to Another Sheet Based On Cell Value

[Madison00]

Hi all,

Complete Excel and VBA newbie here, I'm trying to urgently find a solution to a VBA macro(?) that I need to create for work. Full disclosure, I have been trying to do this entirely via Googling and platforms like this amazing one!

I'm trying to AUTOMATICALLY move a row of data from one sheet titled "Active" to another sheet titled "Complete". I have a table of data that is constantly expanding or shrinking in the Active sheet that goes from columns B-I and starts with headers in row 5 (so first row of actual data is in row 6 - cell B6). I am trying to get a VBA macro(I think this is what its called?) to do the above. I need the data to start copying into open rows on the "Complete" sheet starting in row 8, column B. I need this to run automatically every time the information in column B (in any cell) is updated. And I need it to only move when the cells in column B of the "Active" sheet read "Yes". I have drop down menus in these.

I have attached a photo below for reference. Please let me know if you have any questions and I will respond promptly. I have been trying for days to get this to work with different codings and nothing is working. I'm also struggling to get my macros to appear once I think I've entered an coded them (i.e. I'll click "view Macros" and it will be blank but then I'll click into the VBA editor and there will be code typed?)

Thank you so much for your help in advance!! It is greatly appreciated!

 

[Joe4]

Welcome to the Board!

I need this to run automatically every time the information in column B (in any cell) is updated. And I need it to only move when the cells in column B of the "Active" sheet read "Yes".

How is column B being updated?
Manually, or by formulas, links, or data import?

 

[Joe4]

If column B is being manually updated, I think the following code will do what you want.
Just follow these steps:

1. Go to your "Active" sheet
2. Right-click on the sheet tab name at the bottom of your screen, and select "View Code"
3. Paste the following VBA code in the VB Editor window that pops up

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see only one cell updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if entry is made in column B after row 5 and is set to "Yes"
    If Target.Column = 2 And Target.Row > 5 And Target.Value = "Yes" Then
        Application.EnableEvents = False
'       Copy columns B to I to complete sheet in next available row
        Range(Cells(Target.Row, "B"), Cells(Target.Row, "I")).Copy Sheets("Complete").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
    
End Sub

This is special "event procedure" VBA code that runs automatically when a cell is manually updated.
So as you set values in column B to "Yes", it should automatically run.

 

[Madison00]

If column B is being manually updated, I think the following code will do what you want.
Just follow these steps:

1. Go to your "Active" sheet
2. Right-click on the sheet tab name at the bottom of your screen, and select "View Code"
3. Paste the following VBA code in the VB Editor window that pops up

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see only one cell updated
    If Target.CountLarge > 1 Then Exit Sub
   
'   Check to see if entry is made in column B after row 5 and is set to "Yes"
    If Target.Column = 2 And Target.Row > 5 And Target.Value = "Yes" Then
        Application.EnableEvents = False
'       Copy columns B to I to complete sheet in next available row
        Range(Cells(Target.Row, "B"), Cells(Target.Row, "I")).Copy Sheets("Complete").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
   
End Sub

This is special "event procedure" VBA code that runs automatically when a cell is manually updated.
So as you set values in column B to "Yes", it should automatically run.

Cells there are essentially being manually updated. They are being selected from a drop down menu that has the options "Yes" or "No"

 

[Joe4]

Cells there are essentially being manually updated. They are being selected from a drop down menu that has the options "Yes" or "No"

OK, then the code I posted for you in my previous reply should do what you want.

 

[Madison00]

Cells there are essentially being manually updated. They are being selected from a drop down menu that has the options "Yes" or "No"

If column B is being manually updated, I think the following code will do what you want.
Just follow these steps:

1. Go to your "Active" sheet
2. Right-click on the sheet tab name at the bottom of your screen, and select "View Code"
3. Paste the following VBA code in the VB Editor window that pops up

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see only one cell updated
    If Target.CountLarge > 1 Then Exit Sub
   
'   Check to see if entry is made in column B after row 5 and is set to "Yes"
    If Target.Column = 2 And Target.Row > 5 And Target.Value = "Yes" Then
        Application.EnableEvents = False
'       Copy columns B to I to complete sheet in next available row
        Range(Cells(Target.Row, "B"), Cells(Target.Row, "I")).Copy Sheets("Complete").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
   
End Sub

This is special "event procedure" VBA code that runs automatically when a cell is manually updated.
So as you set values in column B to "Yes", it should automatically run.

This works, THANK YOU SO MUCH!!! I've been trying for days and this just solved it so quickly. I am super grateful!

 

[Joe4]

You are welcome.

If you have any questions about the solution, please feel free to ask them.

Note that the key here is to use "Event Procedure VBA code", which is special VBA code that runs automatically upon some event (like the manual update of particular cells) happening.
There are other event procedures, like the opening of a workbook, the closing of a workbook, the selection of a certain cell, etc.

 

[Madison00]

If column B is being manually updated, I think the following code will do what you want.
Just follow these steps:

1. Go to your "Active" sheet
2. Right-click on the sheet tab name at the bottom of your screen, and select "View Code"
3. Paste the following VBA code in the VB Editor window that pops up

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see only one cell updated
    If Target.CountLarge > 1 Then Exit Sub
   
'   Check to see if entry is made in column B after row 5 and is set to "Yes"
    If Target.Column = 2 And Target.Row > 5 And Target.Value = "Yes" Then
        Application.EnableEvents = False
'       Copy columns B to I to complete sheet in next available row
        Range(Cells(Target.Row, "B"), Cells(Target.Row, "I")).Copy Sheets("Complete").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
   
End Sub

This is special "event procedure" VBA code that runs automatically when a cell is manually updated.
So as you set values in column B to "Yes", it should automatically run.

Hi Joe4 - If I need to reverse it to go backwards (for example if someone accidentally selected yes when it should be no and it moved to the sheet how could I make this code for the "Complete" sheet? I tried to slightly change the values in the code that you had posted for me (again HUGE THANKS!!) so that it would do this and I've managed to make it delete but it hasn't been able to move to the next available row in the "Active" sheet. This is what I put in:

Private Sub Worksheet_Change(ByVal Target As Range)

' Check to see only one cell updated
If Target.CountLarge > 1 Then Exit Sub

' Check to see if entry is made in column B after row 7 and is set to "No"
If Target.Column = 2 And Target.Row > 7 And Target.Value = "No" Then
Application.EnableEvents = False
' Copy columns B to I to active sheet in next available row
Range(Cells(Target.Row, "B"), Cells(Target.Row, "I")).Copy Sheets("Active").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
' Delete current row after copied
Rows(Target.Row).Delete
Application.EnableEvents = True
End If

End Sub

And below is a photo of that worksheet. Would you be able to tell me what I'm doing wrong? Thank you!

 

[Joe4]

I tried out your code, and it works fine for me.
You may want to look a little closer at your Active sheet, and make sure it did not paste it somewhere WAY down the page.

If you go to the last value you can see in column B on the Active sheet, and hit CTRL+Down Arrow, does it go to the line of data you are looking for, way down the sheet?
If so, that means that you already have stuff in column B that while it looks empty, really isn't, at least not to Excel.

 

[Madison00]

I tried out your code, and it works fine for me.
You may want to look a little closer at your Active sheet, and make sure it did not paste it somewhere WAY down the page.

If you go to the last value you can see in column B on the Active sheet, and hit CTRL+Down Arrow, does it go to the line of data you are looking for, way down the sheet?
If so, that means that you already have stuff in column B that while it looks empty, really isn't, at least not to Excel.

Ah, I had empty rows for a table. I've made a note for this. Thank you so much again!!