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!

 

[Spawnacus]

Hi @Joe4,

I have taken the above and altered it for my needs but I can't get it to use another column but B for the Yes/No

I updated it for everything I needed but it always uses Column B for the Yes/No.

Any tips for advice on this front (I have tweaked this a few times.

Inlcuding range copied etc etc but its always B no matter what sheet or workbook I use it in.

Obviously I have been tweaking my workbooks so B is the Yes/No now but I would still wonder why its always B.

Thanks in advance for a 3 year old post solution

 

[Joe4]

You need to change this part:

If Target.Column = 2 ...

That is looking at the 2nd column (which is column B).
So you need to change that number to the numeric index of your desired column (i.e. A=1, B=2, C=3, etc).

 

[LBENNETT]

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).

@Joe4 apologies for the delay in responding. Just wanted to say thanks for your help, although I wasn't able to resolve the problem, I appreciated your support.

 

[Jess1234556]

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 wonder if you can help me. I followed your instructions but amend it slightly, as I'm trying to achieve the same thing. It deletes the row from the active tab, but it doesn't copy it across.

If target.CountLarge > 1 Then Exit Sub
If target.Column = 17 And target.row > 7 And target.Value = "Y" Then
Application.EnableEvents = False
Sheets("Accounts Completed").Cells(Sheets("Accounts Completed").Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(1, 17).Value = _
Range(Cells(target.row, "A"), Cells(target.row, "Q")).Value
Rows(target.row).Delete
Application.EnableEvents = True
End If
End Sub

 

[Joe4]

Hi Joe,
I wonder if you can help me. I followed your instructions but amend it slightly, as I'm trying to achieve the same thing. It deletes the row from the active tab, but it doesn't copy it across.

If target.CountLarge > 1 Then Exit Sub
If target.Column = 17 And target.row > 7 And target.Value = "Y" Then
Application.EnableEvents = False
Sheets("Accounts Completed").Cells(Sheets("Accounts Completed").Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(1, 17).Value = _
Range(Cells(target.row, "A"), Cells(target.row, "Q")).Value
Rows(target.row).Delete
Application.EnableEvents = True
End If
End Sub

Welcome to the Board.

It is best to start your own thread, instead of posting to an old thread with over 40 replies!
In the new thread, it would be helpful if you showed an example of what exactly you are trying to copy, and showed what it is actually copying.