Automatically Move Row to Another Sheet Based On Cell Value

Madison00

New Member
Joined
Jul 6, 2021
Messages
5
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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!
 

Attachments

  • 7-6-2021 11-12-51 AM.png
    7-6-2021 11-12-51 AM.png
    31.7 KB · Views: 2,969

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
HI Joe,

I need you help for similar query.

I want the row to be moved completed if the value is either Yes or Yes-Processed. I am new to VBA and I applies Or condition but didn't work. Can you please help.
 
Upvote 0
HI Joe,

I need you help for similar query.

I want the row to be moved completed if the value is either Yes or Yes-Processed. I am new to VBA and I applies Or condition but didn't work. Can you please help.
If you used the code in my original post, specifically this line:
VBA Code:
   If Target.Column = 2 And Target.Row > 5 And Target.Value = "Yes" Then
you would need to use OR, but also parentheses, like this:
VBA Code:
   If Target.Column = 2 And Target.Row > 5 And (Target.Value = "Yes" Or Target.Value = "Yes-Processed") Then
so that it takes proper priority in reference to the other AND conditions.
 
Upvote 0
Hi Joe,
I just came across this VBA and it's working great. As a complete newbie to VBA. THANK YOU for this

In my case, I have conditional formatting on the source sheet that I don't want to copy over to my "history" sheet. Can I add something in the code to copy/paste the values only?
 
Upvote 0
Hi Joe,
I just came across this VBA and it's working great. As a complete newbie to VBA. THANK YOU for this

In my case, I have conditional formatting on the source sheet that I don't want to copy over to my "history" sheet. Can I add something in the code to copy/paste the values only?
Welcome to the Board!

Can you post the adaptation of the code that you are using right now?
 
Upvote 0
VBA Code:
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 J after row 1 and is set to "Live"
    If Target.Column = 10 And Target.Row > 1 And Target.Value = "Live" Then
        Application.EnableEvents = False
'       Copy columns A to K to complete sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "K")).Copy Sheets("History Book").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
    
End Sub

Here is the code I'm using. I changed it to make it relevant to the columns and value I needed. It works great, I just don't want all the fill colors in my "history book" sheet

Thank you!
 
Upvote 0
Try this modification (which will only paste the values):
VBA Code:
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 J after row 1 and is set to "Live"
    If Target.Column = 10 And Target.Row > 1 And Target.Value = "Live" Then
        Application.EnableEvents = False
'       Copy columns A to K to complete sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "K")).Copy
        Sheets("History Book").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial _
            Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
    
End Sub
 
Upvote 0
Hi So, I am also new to advance Excel and am trying to do the same thing. I want the row to automatically move based on the status to the correct tab...is that possible?
 
Upvote 0

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top