Run-Time error 1004 - VBA -

Question

Hopefully an easy one to answer... no idea why I am getting this issue and I can't figure it out.  

Oddly, the code runs fine through "Step 1", but throws and error on "Step 2" (Run-time error '1004' Application defined or object-defined error".  Even more interesting is that when I go to debug, I find that Worksheets(posSheetName).Range("L:U").Value has actually been updated correctly!

Code:

Private Sub CmdPos()

Dim LongShort, rowB As Integer, r as Long

Application.ScreenUpdating = False

Worksheets("POS").Cells.Clear

Worksheets("POS").Range("A:N").Value = Worksheets("bluebook").Range("A:N").Value  'Step 1 - no issues
 

Worksheets("POS").Activate

Range("A:D,F:F,M:M").EntireColumn.Delete



Cells.Sort Key1:=Range("F1")
LongShort = Cells(Rows.Count, "F").End(xlUp).row
    
    With Range("F1:F" & LongShort)
        .AutoFilter Field:=1, Criteria1:="=C", Operator:=xlOr, Criteria2:="=D"
        .Offset(1).Resize(LongShort).EntireRow.Delete
        .AutoFilter
    End With

'Delete new Column F and I(unused data)
Range("F:F,I:I").EntireColumn.Delete

r = Cells(Rows.Count, 1).End(xlUp).row
    With Sheets("POS")
        Range("C2:C" & r) = Range("C2:C" & r).Value
        For r = r To 2 Step -1
            If Cells(r, 3) = Cells(r - 1, 3) And Cells(r, 6) = Cells(r - 1, 6) Then
                Cells(r - 1, 2) = Cells(r, 2) + Cells(r - 1, 2)
                Cells(r - 1, 5) = Cells(r, 5) + Cells(r - 1, 5)
                Cells(r, 3).EntireRow.Delete xlUp
            End If
        Next r
    End With

Worksheets("POS").Range("L:U").Value = Worksheets("yes").Range("A:J").Value 'Step 2 - Runtime error 1004



Application.CutCopyMode = False
ActiveWorkbook.Save



Application.ScreenUpdating = True
End Sub

Answer 1

It is impossible to re-create your error without the workbook - the code itself works fine, so there is something about the two ranges

Worksheets("POS").Range ("L:U")
and
Worksheets("yes").Range ("A:J")

that is causing the error - merged cells, objects, weird errors, workbook corruption...

Anyways, try changing

Worksheets("POS").Range("L:U").Value = Worksheets("yes").Range("A:J").Value

to
With Worksheets("yes")
    Intersect(.Range("A:J"), .UsedRange).Copy
    Worksheets("POS").Range("L1").PasteSpecial xlPasteValues
End With

Answer 2

Thanks, Bernie.  For a range with 2000-3000 rows of data, would copy and pasting result in decreased performance?  I've been trying to avoid using the clipboard, due to what I *think* is possible memory leak from some part of this or another sub.

Answer 3

I have not noticed a difference, as long as you do complete ranges at once. I think part of your problem may be that the code is actually affecting millions of cells when you use the Column.value = column.Value version.  You also use this line, which helps:

Application.CutCopyMode = False