PasteSpecial method of Range class failed

[azizrasul]

I get the error 'PasteSpecial method of Range class failed' on line

.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

in the code below.

Essentially I'm creating a pivot table in Sheet2 whose results I want to transfer to another sheet. The code is being run from a label on a sheet called 'Dashboard'.

    Sheets("Sheet1").Range("A1:D" & Sheets("Sheet1").UsedRange.Rows.Count).Select
    Sheets.Add
    Sheets(Sheets.Count - 1).Name = "Sheet2"
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1!R1C1:R" & Sheets("Sheet1").UsedRange.Rows.Count & "C4", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion12
    
    With Sheets("Sheet2")
        With .Cells(3, 1)
            ActiveWorkbook.ShowPivotTableFieldList = True
        End With
    End With
    
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Location")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables("PivotTable3").PivotFields("Star Rating"), "Sum of Star Rating", xlSum
    Debug.Print Sheets("Sheet2").UsedRange.Rows.Count
    Sheets("Sheet2").Range("A4:A" & Sheets("Sheet2").UsedRange.Rows.Count + 1).Copy
    
    With Sheets("DETAILED - KPI 5")
        With .Range("B130:I200")
            .ClearContents
        End With
        With .Range("B130")
            .PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
    End With

 

[Norie]

I don't think anything is actually available to copy.

That's because of the ClearContents code between the copy and paste.

 

[azizrasul]

Silly me. Thanks.