PasteSpecial method of Range class failed, but with a normal copy it works-CodePudding

I try to copy the values of a table on one Sheet to a table on another sheet. If I just do a "normal" copy, everything works fine. But if I try to do a pastespecial, a run-time error '1004' appears saying: 'PasteSpecial method of Range class failed'

The code that works:

Dim StatusCol As Range
Dim Status As Range
Dim PasteCell As Range


Set StatusCol = Sheet1.Range("I2:I100") 
Set PasteCell = Sheet2.Range("B8") 

For Each Status In StatusCol
    
    If Status = "> 1000 TEUR" Then Status.Offset(0, -7).Copy PasteCell
    
    If Status = "> 1000 TEUR" Then Set PasteCell = PasteCell.Offset(1, 0)

Next Status

The code that does not work:

Dim StatusCol As Range
Dim Status As Range
Dim PasteCell As Range


Set StatusCol = Sheet1.Range("I2:I100") 
Set PasteCell = Sheet2.Range("B8") 

For Each Status In StatusCol
    
    If Status = "> 1000 TEUR" Then Status.Offset(0, -7).Copy
    PasteCell.PasteSpecial xlPasteValues
    
    If Status = "> 1000 TEUR" Then Set PasteCell = PasteCell.Offset(1, 0)

Next Status

Thank you in advance!

CodePudding user response：

I guess your program does not fail when it should copy something, it fails when it shouldn't. You Copy the value only under a certain condition (Status = "> 1000 TEUR") but issuing the PasteSpecial in any case. You get the error when you try to Paste something without copying something before.

Your code could look like

If Status = "> 1000 TEUR" Then 
    Status.Offset(0, -7).Copy
    PasteCell.PasteSpecial xlPasteValues
    Set PasteCell = PasteCell.Offset(1, 0)
End If

However, if you want to copy only values, it is much better (much faster) to assign the value directly. So better use:

If Status = "> 1000 TEUR" Then 
    PasteCell.Value = Status.Offset(0, -7).Value
    ' Also allowed: PasteCell = Status.Offset(0, -7)
    Set PasteCell = PasteCell.Offset(1, 0)
End If