Run-time error '1004': PasteSpecial method of Range class failed

[level3ninja]

Hi.

I'm using Excel 2010, Windows 7.
For the life of me I cannot understand the logic behind pasting using vba in excel.
Anyhow, I'm getting the above error on the following macro:

Sub Heading_Relining_Up()
Dim rownumber As Long
Dim rcount As Long
 
rownumber = 0
 
rcount = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count
 
Do
     Range("D1").Offset(rownumber).Select
    If ActiveCell = "" Then
        rcount = rcount + 1
    Else
        If ActiveCell.Offset(0, 1) = "H" Then
            ActiveCell.Cut
            ActiveCell.Offset(0, -1).Select
            ActiveCell.PasteSpecial (PasteAsXLPasteType = xlPasteAll)
            ActiveCell.Offset(0, 2).Cut
            ActiveCell.Offset(0, 3).Select
            ActiveCell.PasteSpecial (xlPasteValues)
            rownumber = rownumber + 1
        Else
            rownumber = rownumber + 1
        End If
    End If
     
     
Loop Until rownumber = rcount
 
Range("A1").Select
 
ExitSub:
End Sub

The following line is highlighted when I Debug:
"ActiveCell.PasteSpecial (PasteAsXLPasteType = xlPasteAll)"

I have tried numerous different methods of pasting, but there must be something I'm missing that will allow all manner of paste methods to be used after it's fixed.

Thanks for your time.

 

[bto.zapata]

HI! have you try using ActiveCell.PasteSpecial xlPasteValues without parenthesis? your code worked for me with that...

 

[level3ninja]

Hi! Thanks for replying
I tried it, but still get the same error.

(I also realised I made a mistake in the code, that made it loop if there was a blank cell in col D.
Here's the fixed version:

Sub Heading_Relining_Up()
Dim rownumber As Long
Dim rcount As Long
 
rownumber = 0
 
RowCount = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count
 
Do
     Range("D1").Offset(rownumber).Select
    If ActiveCell = "" Then
        rownumber = rownumber + 1
    Else
        If ActiveCell.Offset(0, 1) = "H" Then
            ActiveCell.Cut
            ActiveCell.Offset(0, -1).Select
            ActiveCell.PasteSpecial xlPasteAll
            ActiveCell.Offset(0, 2).Cut
            ActiveCell.Offset(0, 3).Select
            ActiveCell.PasteSpecial xlPasteValues
            rownumber = rownumber + 1
        Else
        End If
        rownumber = rownumber + 1
    End If
     
     
Loop Until rownumber = rcount
 
Range("A1").Select
 
ExitSub:
End Sub

 

[level3ninja]

For some reason I had mixed up rcount and rowcount when I had changed from one to the other.

Thought that had fixed it, but it hasn't. Still having the same issue.