VBA code needed for row movement and empty ones moved

June 22, 2018 at 09:26:48
Specs: Windows 10
Can anyone help me write a Macro that will move rows in excel to another worksheet if "Yes" is entered into column "F"?
I have three sheets "First Review" "Second Review and "Memorialized". I want a row to automatically move from the first to the second and a column from the second to the third. The rows on the second review and memorialized sheets remaining are to move up so there are no empty rows.

I attempted to use the code submitted by Derby Dad. I changed 9 to 6 and I to F and changed the names of the sheets. But nothing is happening once I put in test data to test the code. Any response will be appreciated.


See More: VBA code needed for row movement and empty ones moved

Report •

#1
June 22, 2018 at 09:36:07
re: "I attempted to use the code submitted by Derby Dad."

DerbyDad (me) has written literally hundreds (thousands?) of pieces of code in this forum. There is no way that he (me) could possibly remember what code you are referring to. "I changed 9 to 6 and I to F" means nothing to us (me) at this point.

It might help if you included a link to the thread that you found the code in. It might also help if you included your modified version of the code so that we know exactly what changes you made.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#2
June 22, 2018 at 10:33:37
I'll go searching for the original thread in a second but here's the modified code:

Modified code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
If Target = "Yes" Then
Application.EnableEvents = False
nxtRow = Sheets("SecondReview").Range("F" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy _
Destination:=Sheets("SecondReview").Range("B" & nxtRow)
Target.EntireRow.Delete
End If
End If
Application.EnableEvents = True
End Sub


Report •

#3
June 22, 2018 at 10:35:49
First, a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link. (indents, etc)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Related Solutions

#4
June 22, 2018 at 11:41:08
Please see below.

message edited by Dnverrett


Report •

#5
June 22, 2018 at 11:41:37
Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 6 Then
   If Target = "Yes" Then
    Application.EnableEvents = False
       nxtRow = Sheets("Memoralized").Range("F" & Rows.Count).End(xlUp).Row + 1
        Target.EntireRow.Copy _
           Destination:=Sheets("Memoralized").Range("A" & nxtRow)
        Target.EntireRow.Delete
     End If
  End If
  Application.EnableEvents = True
End Sub

message edited by Dnverrett


Report •

#6
June 22, 2018 at 11:50:34
That's not the same code as you posted before. I see that you fixed the Range("B" & nxtRow) problem. That would have certainly caused an error.

In fact, I'll bet that that is the root cause of your current problem: "nothing is happening"

Here’s my guess from afar.

1 – You attempted to run the code and it produced an error.
2 – It produced the error after the Application.EnableEvents = False instruction was executed and then the code stopped.
3 – The Application.EnableEvents = True instruction was not executed.

If that happened, then Events have been disabled at the Application level and the Worksheet_Change code is not being triggered.

Close and reopen Excel or run this code to re-enable events:

Sub EventsOn()
  Application.EnableEvents = True
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#7
June 25, 2018 at 05:20:38
Thank you for your assistance. The other thing that I discovered is that the code does not work if I'm selecting Yes from a list. Is there a way to modify the code to accept or see "Yes" when selected from a list?

Report •

#8
June 25, 2018 at 11:10:23
First, I'm not sure why you marked the thread as Solved if the code is still not working for you. I have reset the Best Answer for now.

Second, you'll have to explain what you mean by "the code does not work". Does it not run? Does is throw up an error? Does it run but not copy/delete the Target row? Please keep in mind that we can't see your spreadsheet from where we're sitting, so you need to be specific in your posts. All that we have to work with is what you tell us so we need a bit more detail.

For what it's worth, I created a sheet with a "Yes/No" drop down in Column F. Using the exact code that you posted in Response #5 the Target Row is copied to the sheet named Memoralized and then deleted when I choose Yes via the drop down.

P.S. You did not spell "Memorialized" the same way in your code as you did in your original post.

Memorialized vs. Memoralized

Is that an error or was it done on purpose?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

Ask Question