Simple Row Cut & Paste Not Working For Some Reason

stildawn · Jan 29th 2017

Hi Al

I'm sure I have used this code numerous times in the past but for some reason its not working.

I'm getting a 1004 error on this code:

Code

Sub SplitNew()


'#### Copies Header ####
ThisWorkbook.Sheets("Inbound").Rows("1:1").Copy
ThisWorkbook.Sheets("New").Range("A1").PasteSpecial xlValues




'#### Copies Only New Rows ####
Lastrow = ThisWorkbook.Sheets("Inbound").Range("A" & ThisWorkbook.Sheets("Inbound").Rows.Count).End(xlUp).Row
For i = Lastrow To 2 Step -1
    If ThisWorkbook.Sheets("Inbound").Range("T" & i).Value = "N" Then
        LastrowN = ThisWorkbook.Sheets("New").Range("A" & ThisWorkbook.Sheets("New").Rows.Count).End(xlUp).Row
        ThisWorkbook.Sheets("Inbound").Rows(i & ":" & i).Cut
        ThisWorkbook.Sheets("New").Range("A" & LastrowN + 1).PasteSpecial xlValues 'ERROR HERE
    Else
    End If
Next i




End Sub

Display More

It doesn't matter how I rework it, I still get errors, for example if I do a simple "paste" then I get 438 errors:

Code

Sub SplitNew()


'#### Copies Header ####
ThisWorkbook.Sheets("Inbound").Rows("1:1").Copy
ThisWorkbook.Sheets("New").Range("A1").PasteSpecial xlValues




'#### Copies Only New Rows ####
Lastrow = ThisWorkbook.Sheets("Inbound").Range("A" & ThisWorkbook.Sheets("Inbound").Rows.Count).End(xlUp).Row
For i = Lastrow To 2 Step -1
    If ThisWorkbook.Sheets("Inbound").Range("T" & i).Value = "N" Then
        LastrowN = ThisWorkbook.Sheets("New").Range("A" & ThisWorkbook.Sheets("New").Rows.Count).End(xlUp).Row
        ThisWorkbook.Sheets("Inbound").Rows(i & ":" & i).Cut
        ThisWorkbook.Sheets("New").Range("A" & LastrowN + 1).Paste 'ERROR HERE
    Else
    End If
Next i




End Sub

Display More

If I change the Rows to a range and the Range to a rows still get the errors.

Any ideas, this should work.

Thanks

**KjBox** · Jan 30th 2017

Re: Simple Row Cut & Paste Not Working For Some Reason

Try

Code

Sub SplitNew()
    Dim i As Long, ii As Long, wsI As Worksheet, wsN As Worksheet
    Set wsI = Sheets("Inbound"): Set wsN = Sheets("New")
    
    Application.ScreenUpdating = 0
    With wsI.Cells(1).CurrentRegion
        .Rows(1).Copy wsN.[a1]
        i = .Rows.Count
        For ii = i To 2 Step -1
            If .Cells(ii, 20) = "N" Then
                .Rows(ii).Copy wsN.Cells(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 1)
                .Parent.Rows(ii).Delete
            End If
        Next
    End With
    
End Sub

Display More

stildawn · Jan 30th 2017

Re: Simple Row Cut & Paste Not Working For Some Reason

I fixed a referencing bit in your code to the below and it worked:

Code

Dim i As Long, ii As Long, wsI As Worksheet, wsN As Worksheet    Set wsI = Sheets("Inbound"): Set wsN = Sheets("New")
     
    With wsI.Cells(1).CurrentRegion
        .Rows(1).Copy wsN.[a1]
        i = .Rows.Count
        For ii = i To 2 Step -1
            If .Cells(ii, 20) = "N" Then
                .Rows(ii).Copy wsN.Cells(wsN.Cells(wsN.Rows.Count, 1).End(xlUp).Row + 1, 1)
                .Parent.Rows(ii).Delete
            End If
        Next
    End With

Display More

Don't know why yours worked and not mine though.

Thanks though

**KjBox** · Jan 30th 2017

Re: Simple Row Cut & Paste Not Working For Some Reason

You're welcome.

Notice I did not cut the row from "Inbound", when you cut a row you need to Insert that row into another sheet not use PasteSpecial.

I find it easier to copy the row to the new sheet then delete the original row.

**KjBox** · Jan 30th 2017

Re: Simple Row Cut & Paste Not Working For Some Reason

I don't see any referencing that you fixed, all you did was remove the turning off of ScreenUpdating.

Turning off ScreenUpdating makes the code run faster and stops screen flickering.

stildawn · Feb 2nd 2017

Re: Simple Row Cut & Paste Not Working For Some Reason

This part:

(wsN.Cells(wsN.Rows.Count, 1).End(xlUp).Row + 1, 1)

The original wasn't finding the new lastrow on the correct sheet.

Good to know about the cut command, I'll be sure to remember that in future. I usually copy and then delete as well but thought why not just have one line instead of two.

**KjBox** · Feb 2nd 2017

Re: Simple Row Cut & Paste Not Working For Some Reason

Ahh yes I see it now

My bad for leaving that out!

stildawn · Feb 2nd 2017

Re: Simple Row Cut & Paste Not Working For Some Reason

All good simple fix

Thanks again

Simple Row Cut & Paste Not Working For Some Reason

Participate now!

Share