Solved Paste cut row to a certain row on another worksheet

April 11, 2017 at 17:00:56
Specs: Windows 7
I would like to know what macro in excel i could use to add to my existing code that would look at the number value on the first cell of the row that is cut and paste on another worksheet to the row number that it identified in the first cell. so if the first cell in the cut row was 5 then i would like it to insert that row to row 6 (row 1 is a header row). here is the code i have so far. the code works perfect if i want the row to be pasted on the last row of the new worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim i As Variant
    Dim endrow As Integer
    Dim CurrentTasks As Worksheet, Complete As Worksheet

    Set CurrentTasks = ActiveWorkbook.Sheets("CurrentTasks")
    Set Complete = ActiveWorkbook.Sheets("Complete")

    endrow = Complete.Range("A" & Complete.Rows.Count).End(xlUp).row

    For i = 2 To endrow
        If Complete.Cells(i, "L").Value = "In Progress" Then
           Complete.Cells(i, "L").EntireRow.Cut _
              Destination:=CurrentTasks.Range("A" & _
              Complete.Rows.Count).End(xlUp).Offset(1)
           Complete.Cells(i, "L").EntireRow.Delete
        End If
    Next
End Sub


See More: Paste cut row to a certain row on another worksheet

Report •

✔ Best Answer
April 12, 2017 at 15:04:16
Private Sub Worksheet_Change(ByVal Target As Range)
    'Determine if "In Progress" was enteed in a single cell in Column L
 If Target.Cells.Count = 1 Then
   If Target.Column = 12 Then
     If Target = "In Progress" Then
On Error GoTo EventsTrue
'Disable Events to prevent code from firing again
       Application.EnableEvents = False
'Determine value for Destination Row
        dstRow = Cells(Target.Row, "A") + 1
'Copy, Insert, Paste, Delete
         Target.EntireRow.Copy
         Sheets("CurrentTasks").Range("A" & dstRow).Insert Shift:=xlDown
         Target.EntireRow.Delete
'Enable Events
EventsTrue:
       Application.EnableEvents = True
     End If
   End If
 End If
End Sub

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



#1
April 11, 2017 at 20:46:23
If I understand your requirements correctly, the answer to your direct question is this:

Destination:=CurrentTasks.Range("A" & Complete.Cells(i, "A")))

Since Row i is the row that is cut, it sounds like you want to use the value in Range("A" & i) as the destination Row.

Let me know if I understood your needs correctly, but I have another question, actually 2.

1 - Why are you looping through every Row in Sheets("Complete") whenever any change is made to the sheet? It seems to me that once any Row that contained "In Progress" in Column L has been moved, the only time the code really needs to do anything is when "In Progress" is entered in Column L. The Loop is never going to find more than one "In Progress" and it already knows where it can be found: in the Target row, the one that was changed. Why not just move the Target row when "In Progress" is entered in Column L?

2 - Are you aware that the code fires 3 times, looping through every row each time, whenever "In Progress" is entered into the Column L?

It fires once at the Change is made by the user, then it fires again when the code Cuts the row (that counts as a Change) and then it fires a 3rd time when the Target row is deleted. That's also a Change.

Are you familiar with these 2 instructions?

Application.EnableEvents = False

and

Application.EnableEvents = True

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


Report •

#2
April 12, 2017 at 10:12:31
Thank you for your reply! To be honest I am a beginner to coding with VBA and macros. I am trying to teach myself but as you can see I need some serious help. I didn't know about the looping 3 times but how you mentioned it I can see what you mean. Your statements are bang on and brilliant. Yes all I need is that when the cell in column L reads "In Progress" that it cuts that row and pastes it from sheet "Complete" back to its original sheet "Current Tasks". The thing is The sheet "Current Tasks" is sorted by ticket number so I would like the row that is cut from sheet "complete" inserted back into the sequence of the sort instead of at the bottom of the sheet. The row it needs to be inserted is identified in the A cell of the cut row , well that number plus one because row one is a header row.

Report •

#3
April 12, 2017 at 11:30:29
First, let me offer you this link which may help you when writing your own code or when reverse engineering any code that you find from other sources:

https://www.computing.net/howtos/sh...

Next, let me warn you about the Application.EnableEvents = False instruction.

The warning:

If the code fails after the False instruction is executed but before the True instruction is executed, Events will be disabled and nothing will happen when you make a change to the sheet. This can happen quite often when you are writing code and it throws up an error, causing you to stop the macro. The next time you make a change to the sheet, you might think that the code is not working when in reality it isn't even running because Events have been disabled. There is no warning or indication that Events are disabled, other than the fact that Event driven macros don't run.

There are 3 options for re-enabling Events if that happens:

1 - Brute force - Quit excel and restart it. (Not usually the best option.)

2 - With a simple macro -
Stick this code in the VBA editor and run it whenever you need to re-enable Events:

Sub ResetEvents()
  Application.EnableEvents = True
End Sub

3 - The VBA Immediate window - The VBA Editor (VBE) has a feature know as the Immediate Window. It can be used to run instructions without them being part of a macro. If you put this instruction (or any instruction) in the Immediate window, you can just put your cursor on it and hit enter. The instruction will run and Events will be enabled:

Application.EnableEvents = True

Obviously you can also put the False instruction in the Immediate window so that you can turn Events off so you can make changes to the sheet without the code running every time. Just make sure to turn Events back on when you are done.

With all that said, try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Determine if "In Progress" was enteed in a single cell in Column L
 If Target.Cells.Count = 1 Then
   If Target.Column = 12 Then
     If Target = "In Progress" Then
On Error GoTo EventsTrue
'Disable Events to prevent code from firing again
       Application.EnableEvents = False
'Determine value for Destination Row
        dstRow = Cells(Target.Row, "A") + 1
'Cut, Paste, Delete
         Target.EntireRow.Cut _
              Destination:=Sheets("CurrentTasks").Range("A" & dstRow)
         Target.EntireRow.Delete
'Enable Events
EventsTrue:
       Application.EnableEvents = True
     End If
   End If
 End If
End Sub

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

message edited by DerbyDad03


Report •

Related Solutions

#4
April 12, 2017 at 14:44:59
This code is almost perfect but i need it to insert a row and then paste. as the code stands right now it is pasting over the data that is currently in the target location. by the way thank you for all your help very much appreciated.

Report •

#5
April 12, 2017 at 15:04:16
✔ Best Answer
Private Sub Worksheet_Change(ByVal Target As Range)
    'Determine if "In Progress" was enteed in a single cell in Column L
 If Target.Cells.Count = 1 Then
   If Target.Column = 12 Then
     If Target = "In Progress" Then
On Error GoTo EventsTrue
'Disable Events to prevent code from firing again
       Application.EnableEvents = False
'Determine value for Destination Row
        dstRow = Cells(Target.Row, "A") + 1
'Copy, Insert, Paste, Delete
         Target.EntireRow.Copy
         Sheets("CurrentTasks").Range("A" & dstRow).Insert Shift:=xlDown
         Target.EntireRow.Delete
'Enable Events
EventsTrue:
       Application.EnableEvents = True
     End If
   End If
 End If
End Sub

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


Report •

#6
April 12, 2017 at 15:24:21
DerbyDad03 I am forever in your debt, i hope i can learn enough to get as good as you. This really helped me out! Thank you so Much!

Report •

#7
April 12, 2017 at 15:29:31
Try the debugging techniques in the tutorial I gave you the link for. Reverse engineer my code. It's a great way to learn VBA.

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


Report •

#8
April 18, 2017 at 14:27:52
I am getting a compile error for some reason now. it says can't find project or library.

message edited by MattExcel


Report •

#9
April 18, 2017 at 17:47:09
Obviously something has changed since the last time the code worked. Did you change anything in your code or your Excel app?

Do you know what line is causing the error?

Did you try any of the debugging techniques in the tutorial I linked to?

Did you Google the error for possible solutions?

Since I'm not getting any errors I can't offer much more help. If you are willing to post your workbook to zippyshare.com, I can take a look at it. Upload it and then post the link back here in the forum.

Make sure you remove any personal/confidential information before uploading.

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


Report •

#10
April 18, 2017 at 18:24:10
I googled it, nothing changed except for the computer I was working on. I pressed alt f11 and went under tools tab selected references and removed the tick from "Missing: ..." and that got rid of the issue.

Report •

#11
April 18, 2017 at 20:24:36
re: nothing changed except for the computer I was working on.

That's not nothing...that's about as big a change as you can make. A different computer means a different instance of Excel also. I'm not surprised it was a missing reference issue, but obviously I had no way to test that.

Glad you found it.

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


Report •

Ask Question