Solved Moving Row To Different Worksheet

February 23, 2016 at 12:43:41
Specs: Windows 7
I'm trying to move a row from one worksheet to another upon drop down.

I found this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 Then
If Target = "TD" Then
Application.EnableEvents = False
nxtRow = Sheets("TD Locks").Range("I" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy _
Destination:=Sheets("TD Locks").Range("A" & nxtRow)
Target.EntireRow.Delete
End If
End If
Application.EnableEvents = True

If Target.Column = 9 Then
If Target = "Closed" Then
Application.EnableEvents = False
nxtRow = Sheets("Closed Locks").Range("I" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy _
Destination:=Sheets("Closed Locks").Range("A" & nxtRow)
Target.EntireRow.Delete
End If
End If
Application.EnableEvents = True
End Sub


It seems to work, but is throwing errors a run time error '424' Object required.

Also, I have all the sheets set up as tables so that I am able to sort them. I'd like the row to copy to the other page and create a new table row instead of inserting underneath and then me having to drag the table down.

If anyone could help, that would be awesome! Thanks!


See More: Moving Row To Different Worksheet

Report •


#1
February 24, 2016 at 06:44:24
✔ Best Answer
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to use the pre tags to post VBA code in this forum. Posting code within the pre tags will retain the indents and make the code easier for us to read.

Second, you might find this How-To helpful. It can not only help you troubleshoot code that is not working, but can also help you understand how code that is working is doing what it does. By reverse engineering code that you find on the web, you can learn a great deal about writing VBA code.

I used some of these techniques to find the problem with your code.

http://www.computing.net/howtos/sho...

OK, as for your problem, there are a couple of issues with the code.

The main reason that you are getting the Object Required error is because you are deleting the Target when TD is found. Then when your code flows through to the second "If Target.Column = 9" there is no Target, i.e. no Object for VBA to work with.

Another problem with the code is that it is somewhat inefficient. Even if you eliminated the Object Required error, the code would still flow through to the "Closed" section and execute some of the instructions. In other words, if the Target did equal "TD", there is no need to run any instructions related to Closed, such as checking the Target Column (again).

The following code should work a little better. The changes to the first section eliminate the Object Required error by exiting the Sub after executing the instructions if TD was found. There is no need to check for Closed if TD was found. If the code doesn't have to check for an Object that no longer exists, it won't present the error.

The other change I made was to eliminate the second test for Target.Column = 9. Once the code has determined that the Target column was 9, it can then check for TD or Closed and perform the associated instructions depending on which one was found.

I also added a few comments. Comments help not only "us" (those that are trying to help you) but can also be very helpful to you when you come back to this code a year from now and try to figure out what you are doing with it. I know this code is fairly simple, but using comments is a good habit to get into. When you have macros that are really long and complicated, trying to remember what you were thinking X years ago can be difficult.

A wise man once said: "Code tells us how, comments tell us why."

Give this version a go:

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine Target Colunm
 If Target.Column = 9 Then
'Check for "TD", Copy/Delete Row if found
  If Target = "TD" Then
    Application.EnableEvents = False
      nxtRow = Sheets("TD Locks").Range("I" & Rows.Count).End(xlUp).Row + 1
      Target.EntireRow.Copy _
        Destination:=Sheets("TD Locks").Range("A" & nxtRow)
      Target.EntireRow.Delete
      Application.EnableEvents = True
       Exit Sub
  End If

'Check for "Closed", Copy/Delete Row if found
  If Target = "Closed" Then
    Application.EnableEvents = False
      nxtRow = Sheets("Closed Locks").Range("I" & Rows.Count).End(xlUp).Row + 1
      Target.EntireRow.Copy _
        Destination:=Sheets("Closed Locks").Range("A" & nxtRow)
      Target.EntireRow.Delete
      Application.EnableEvents = True
  End If
 End If
End Sub

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


Report •

#2
February 24, 2016 at 06:57:43
Thank you so much DerbyDab03!

You are awesome!! I appreciate your help, and the code works perfectly now!

I appreciate the info about code posting as well as the debugging tutorial link. I will make sure in the future the coding is posted properly!

Also, is there a way that when the row moves to another sheet, that they are included in the current table so that they are easier to sort? Currently I am just pulling the table down every time I move one over.


Report •

#3
February 24, 2016 at 08:34:13
Actually, I'm not sure how to do that. I don't work with tables very much.

Maybe you could record a macro when you modify the table and then clean up the resulting code.

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


Report •

Related Solutions


Ask Question