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!

 

[LBENNETT]

Hello, like many others that have posted on this thread I'm a complete newbie to VBA and hopelessly stuck!! Your responses @Joe4 have been some of the best I've seen on the MrExcel board so I'm hoping you might be able to save me too!

I want to be able to move a row from the Pending tab/worksheet to either the 'Approved', 'On Hold' or 'Declined' tab/worksheet depending on the value in column F (Status) either being 'Approved', 'On Hold' or 'Declined'.

I've tried to follow your previous solutions above but whenever I add the code nothing happens. This is very likely to be my incompetence to change the code to reflect my needs, but I'm hoping you can offer some guidance and potentially a solution! I intend to format this to a table but haven't yet, don't know if that makes a difference. I couldn't squeeze all the columns in the image but the columns go up to AB.

 

[Joe4]

Hello, like many others that have posted on this thread I'm a complete newbie to VBA and hopelessly stuck!! Your responses @Joe4 have been some of the best I've seen on the MrExcel board so I'm hoping you might be able to save me too!

I want to be able to move a row from the Pending tab/worksheet to either the 'Approved', 'On Hold' or 'Declined' tab/worksheet depending on the value in column F (Status) either being 'Approved', 'On Hold' or 'Declined'.

I've tried to follow your previous solutions above but whenever I add the code nothing happens. This is very likely to be my incompetence to change the code to reflect my needs, but I'm hoping you can offer some guidance and potentially a solution! I intend to format this to a table but haven't yet, don't know if that makes a difference. I couldn't squeeze all the columns in the image but the columns go up to AB.

Welcome to the Board!

Where exactly have you placed your code?
Event procedure VBA code (which is automated) ONLY works when placed in the proper sheet module. It will not work if you place it in a newly created General module.
The easiest way to ensure it gets to the correct module is to go to the sheet you want to apply it to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the code in the VB Editor window that pops up.

Also note that it will only run as you manually make updates to the column you are watching (your status column).
If you still cannot get it to work, please post your code (as you would have had to make edits to my original code to get it to work for your situation).

 

[LBENNETT]

Welcome to the Board!

Where exactly have you placed your code?
Event procedure VBA code (which is automated) ONLY works when placed in the proper sheet module. It will not work if you place it in a newly created General module.
The easiest way to ensure it gets to the correct module is to go to the sheet you want to apply it to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the code in the VB Editor window that pops up.

Also note that it will only run as you manually make updates to the column you are watching (your status column).
If you still cannot get it to work, please post your code (as you would have had to make edits to my original code to get it to work for your situation).

Joe, thank you for getting back to me!

Please see below a screenshot of the code - I got to this point by following the steps above. Hopefully you can see from the image that the code is in Sheet5 (Pending).
Thank you for your support.

 

[Joe4]

When posting your code, please do not post images/snapshots of the code - we cannot do anything with that. Please post the code in a manner that allows us to easily copy & paste it to our computer so we can edit it. There are instructions on how to easily use Code tags to post your code so it looks nice and formatted like mine does here: How to Post Your VBA Code

A few questions/clarifications regarding your issue:

1. In your question, you say that the status column is column F, but from your image, it looks like it is column E. Which one is it exactly?

2. How/when do you want this code to run?
Are you trying to run it on a whole bunch of existing data at once (so you would manually need to fire the code)?
Or do you want it to run when you manually update some column (like the "Status" column)? Note if you choose this option, it will fire right away once that column is populated (which might not be what you want if you also have other columns to update before you want to move it).

 

[LBENNETT]

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 = 5 And Target.Row > 1 And Target.Value = "Approved" Then
        Application.EnableEvents = False
'       Copy columns A to AB to complete sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "AB")).Copy Sheets("Approved").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
    
End Sub

Apologies, hopefully the above has worked correctly. Also thanks, was good to learn something new.

1. Sorry I keep moving things around. It's column E. I tried the code with E and F but it didn't make a difference.
2. I'm wanting it to run when I manually update the status column with 'Approved', 'On Hold' or Declined. I'd like the row to go to the respective worksheet depending on the value cell. 'Approved' to the Approved worksheet etc.
The other columns will be populated before the status column is updated so it won't a problem that it fires straight away.

Thanks.

 

[Joe4]

OK, assuming the following is always true:
- For every row with data on your "Pending" sheet, there is data in column A
- The Status drop-down options in column E exactly match sheet names in your workbook

This code should do what you want:

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 E after row 1
    If Target.Column = 5 And Target.Row > 1 Then
'       Make sure value in column E matches one of the 3 options
        If (Target.Value = "Approved") Or (Target.Value = "On Hold") Or (Target.Value = "Declined") Then
            Application.EnableEvents = False
'           Copy row to appropriate sheet in next available row
            Rows(Target.Row).Copy Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'           Delete current row after copied
            Rows(Target.Row).Delete
            Application.EnableEvents = True
        End If
    End If
    
End Sub

 

[LBENNETT]

OK, assuming the following is always true:
- For every row with data on your "Pending" sheet, there is data in column A
- The Status drop-down options in column E exactly match sheet names in your workbook

This code should do what you want:

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 E after row 1
    If Target.Column = 5 And Target.Row > 1 Then
'       Make sure value in column E matches one of the 3 options
        If (Target.Value = "Approved") Or (Target.Value = "On Hold") Or (Target.Value = "Declined") Then
            Application.EnableEvents = False
'           Copy row to appropriate sheet in next available row
            Rows(Target.Row).Copy Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'           Delete current row after copied
            Rows(Target.Row).Delete
            Application.EnableEvents = True
        End If
    End If
   
End Sub

Copied and pasted into the sheet module but no joy!! Do I need to activate anything? I've enabled VBA previously so don't think it's that.
Any thoughts or suggestions would be hugely appreciated.

 

[Joe4]

Note that enabling VBA may be on a Workbook basis!

Try adding this really simple VBA code and running it manually:

Sub MyTest()
    MsgBox "VBA is running!"
End Sub

If you can get it to run and get the message, VBA code has been enabled for your workbook. If you cannot get it to run, then VBA has not been enabled.

Note that the code I gave you should automatically run when you manually change a value in column E.

 

[LBENNETT]

Note that enabling VBA may be on a Workbook basis!

Try adding this really simple VBA code and running it manually:

Sub MyTest()
    MsgBox "VBA is running!"
End Sub

If you can get it to run and get the message, VBA code has been enabled for your workbook. If you cannot get it to run, then VBA has not been enabled.

Note that the code I gave you should automatically run when you manually change a value in column E.

Joe! I have to apologise, I think the organisation I work for may have put a block on any VBAs! Does my thinking align with the disabled options in the image?

I tried the VBA code for the message box but nothing came up, which prompted me to go searching why.

 

[Joe4]

Yep, if that is what you are seeing, I would say it looks like your organization has totally blocked all VBA/Macros!

You may want to see if you can get them to allow Trusted Locations or Digital Signatures (so it only allows workbooks meeting certain criteria to use VBA).