CODEWITHSUNDEEP
c#excel
3xGuy
3xGuy

Reputation: 2559

Paste Values in Excel C#

Hello I have been trying to use the PasteSpecial paste values on another sheet but every thing I've tried I keep receiving the same error.

PasteSpecial method of Range class failed

xl.Application xlApp = Globals.ThisWorkbook.Application;
xl.Workbook wb = xlApp.ActiveWorkbook;
xl.Worksheet data = wb.Sheets["Data"];
xl.Worksheet datasheet = wb.Sheets["DataSheet"];
xl.Worksheet pivot = wb.Sheets["Pivot"];
xl.Range dataRng = data.Range["A1:C" + 
      xlApp.WorksheetFunction.CountA(data.Range["A:A"])];
xl.Range pivotRng = pivot.Range["A1"];

addPivot ap = new addPivot();
ap.DataRange = dataRng;
ap.DestRng = pivotRng;
ap.PivotSheet = pivot;
ap.createPivot("FullName","Date");

pivot.PivotTables("PivotTable1").NullString = "0";
pivot.UsedRange.Copy();   

datasheet.Range["A2:GT999"].ClearContents();  

datasheet.Range["A2"].PasteSpecial(xl.XlPasteType.xlPasteValues, 
                                   xl.XlPasteSpecialOperation.xlPasteSpecialOperationNone, 
                                   System.Type.Missing, System.Type.Missing);

datasheet.Range["2:2"].ClearContents();

I have Tried 

datasheet.Range["A2"].PasteSpecial(xl.XlPasteType.xlPasteValues

datasheet.Range["A2"].PasteSpecial(xl.XlPasteType.xlPasteValues, 
                                   xl.XlPasteSpecialOperation.xlPasteSpecialOperationNone)

and what you see above.

thanks!

Upvotes: 2

Views: 7958

Answers (1)

Byron Wall
Byron Wall

Reputation: 4010

Do the ClearContents before the Copy. Excel is removing your copied range (same as hitting ESC in the program) when you clear those cells out. Then you are trying to PasteSpecial but nothing is in the clipboard any longer.

//...code above

datasheet.Range["A2:GT999"].ClearContents();  

pivot.UsedRange.Copy();

datasheet.Range["A2"].PasteSpecial(xl.XlPasteType.xlPasteValues, 
                                   xl.XlPasteSpecialOperation.xlPasteSpecialOperationNone, 
                                   System.Type.Missing, System.Type.Missing);                                   
//code below...

If you are debugging this step-by-step, you would see the dashed border around the copied range disappear after the ClearContents call. Excel is a bit odd (but predictable) about when the copied range is forgotten. Editing cells anywhere in the Workbook is one sure way to make it forget.

Upvotes: 3

Related Questions