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!

 

[hnryz123]

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 Joe,

I was doing some searching and found your solution to this question. I have more or less the same task I'm trying to accomplish but in my instance "column B" is being updated by formulas. How would I go about getting the VBA code to work with that?

 

[Joe4]

Hi Joe,

I was doing some searching and found your solution to this question. I have more or less the same task I'm trying to accomplish but in my instance "column B" is being updated by formulas. How would I go about getting the VBA code to work with that?

You cannot do that with a "Worksheet_Change" event procedure code - that only works on data that is manually updated.
On data updated with formulas, that would require "Worksheet_Calculate" code, but it isn't that easy, as unlike the "Worksheet_Change" code, VBA cannot tell which cells value changed.

There may be other ways around, if the formulas that are changing are changing due to manual changes to other cells.
Regardless, the solution is going to be distinctly different than what was done here, so you should really start a new thread with your question, and be sure to give a detailed explanation of your data structure, formulas, and desired results (posting a sample would probably be most helpful).

 

[JoeM99]

Hello,

I'm somewhat reviving this thread, as I am having some problems with the code. I copied and pasted the code previously posted, but cannot get mine to work. I've included pictures of both my worksheet and code to help show what's going on.

 

[Joe4]

Welcome to the Board!

How exactly is this not working?
Are you getting errors, or is nothing happening?

Note that this will ONLY automatically run as you add new data in column G - it will NOT run against existing values in column G.
Also, this code MUST be placed in the "Active Projects" Sheet Module in the VBA Editor. If it is in any other module, it will NOT run automatically against this sheet.

And VBA must be enabled for this to run. If you encountered any errors, in the middle of testing where the code did not run to completion, events may have been disabled, which would prevent the code from automatically running again until those events are re-enabled. You can do that by manually running this little piece of code:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

 

[JoeM99]

Welcome to the Board!

How exactly is this not working?
Are you getting errors, or is nothing happening?

Note that this will ONLY automatically run as you add new data in column G - it will NOT run against existing values in column G.
Also, this code MUST be placed in the "Active Projects" Sheet Module in the VBA Editor. If it is in any other module, it will NOT run automatically against this sheet.

And VBA must be enabled for this to run. If you encountered any errors, in the middle of testing where the code did not run to completion, events may have been disabled, which would prevent the code from automatically running again until those events are re-enabled. You can do that by manually running this little piece of code:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

Hi there,

I didn't know that it wouldn't run against existing data. What happened is I had a list of entries, maybe 5-10, and when I updated one of the values to "Completed" nothing happened. Is there a way I can get the code to run based off that? This is a project tracking sheet, so data will be there and be updated from "Not Started" to "In Process" and then to "Completed".

I did put it in the "Active Projects" Sheet Module, but did not get any errors, simply nothing happened.

 

[Joe4]

Hi there,

I didn't know that it wouldn't run against existing data. What happened is I had a list of entries, maybe 5-10, and when I updated one of the values to "Completed" nothing happened. Is there a way I can get the code to run based off that? This is a project tracking sheet, so data will be there and be updated from "Not Started" to "In Process" and then to "Completed".

I did put it in the "Active Projects" Sheet Module, but did not get any errors, simply nothing happened.

I should clarify my previous statement - it will not run on previously entered values for column G. If you change the value in column G to "Complete" (not "Completed"), the code should run.

If it does not, can you upload your file to a file sharing site and provide a link to it here, so we can inspect your workbook?

 

[Kirrash]

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.

I am using this - it is SO amazing. How to change the "trigger" from column B "yes" to instead have column C or D to not be blank. In other words, if a date is entered, the cell is now not blank and will trigger the move?

 

[Joe4]

I am using this - it is SO amazing. How to change the "trigger" from column B "yes" to instead have column C or D to not be blank. In other words, if a date is entered, the cell is now not blank and will trigger the move?

Sorry, I have been away for a bunch of days and am just seeing this now.

You can change this line:

    If Target.Column = 2 And Target.Row > 5 And Target.Value = "Yes" Then

to this:

    If (Target.Column >= 3) And (Target.Column <= 4) And (Target.Value <> "") Then

 

[Kirrash]

Sorry, I have been away for a bunch of days and am just seeing this now.

You can change this line:

    If Target.Column = 2 And Target.Row > 5 And Target.Value = "Yes" Then

to this:

    If (Target.Column >= 3) And (Target.Column <= 4) And (Target.Value <> "") Then

Thank you, Joe!

 

[Joe4]

You are welcome.