CODEWITHSUNDEEP
excelexcel-2010vba
user1382191
user1382191

Reputation: 75

Error in Excel 2010, PasteSpecial method of Range Class failed within macro

Following is my code that's throwing this error:

PasteSpecial method of Range class failed

Error is throwing only when I'm trying to debug.

Range(Cells(7, 1), Cells((Rowrange + 7), 2)).Select
Selection.PasteSpecial Paste:=xlValues


  ' my complete code

strSheetName = "sheet1"
Sheets(strSheetName).Select
B6 = Range("B6").Value
B7 = Range("B7").Value
Range(Cells(11, 1), Cells((Rowrange + 11), 2)).Select
Selection.Copy

strSheetName = "sheet2"
Sheets(strSheetName).Select
' Range(Cells(7, 1), Cells((Rowrange + 7), 2)).Select
'.Range(Cells(7,1), .Cells(RowRange + 7, 2). PasteSpecial Paste := xlValues
'Selection.PasteSpecial Paste:=xlValues
With ActiveSheet
.Range(.Cells(7, 1), .Cells(Rowrange + 7, 2)).PasteSpecial Paste:=xlValues
End With

Is there a way to avoid this error?

Upvotes: 3

Views: 28214

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149277

I believe (If the above is actually your complete code) you are not copying the data and directly trying to do a paste and hence you are getting that error :)

Is this what you are trying?

strSheetName = "Estimated vs Actual Costs"
With Sheets(strSheetName)
    .Range(.Cells(7, 1), .Cells(RowRange + 7, 2)).Copy
    .Range(.Cells(7, 1), .Cells(RowRange + 7, 2)).PasteSpecial Paste:=xlValues
End With

FOLLOWUP

Try this

strSheetName = "sheet1"
With Sheets(strSheetName)
    B6 = .Range("B6").Value
    B7 = .Range("B7").Value
    .Range(.Cells(11, 1), .Cells((RowRange + 11), 2)).Copy
End With

strSheetName = "sheet2"
With Sheets(strSheetName)
    .Range(.Cells(7, 1), .Cells(RowRange + 7, 2)).PasteSpecial Paste:=xlValues
End With

Upvotes: 2

assylias
assylias

Reputation: 328568

The best way to avoid that kind of error is to only use fully qualified ranges:

With Sheets("The name of the sheet")
    .Range(.Cells(7, 1), .Cells(RowRange + 7, 2)).PasteSpecial Paste:=xlValues
End With

Or: With ActiveSheet if you know the sheet is already selected.

Also note that selecting is not necessary.

Upvotes: 1

Related Questions