How to resolve "You can't paste this here because the Copy area and paste area aren't the same size. Select just one cell in the paste area or an area that's the same size, and try pasting again.

Question

We are using excel workbook having multiple worksheet (around 35 sheets) with data (around 84 rows and till AH columns). The data includes alphabets, numbers, formulas, images and filters applied.

We are using below code to copy and paste data of an excel workbook in order to create a new workbook.

               ExcelAPI.Workbook activeWorkbook = ExcelApp.ActiveWorkbook;

activeWorkbook.Application.DisplayAlerts = false;

foreach (ExcelAPI.Worksheet wkSheet in activeWorkbook.Worksheets)

{

wkSheet.Activate();

wkSheet.Cells.Copy();

wkSheet.Cells.PasteSpecial(ExcelAPI.XlPasteType.xlPasteValues, ExcelAPI.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);

wkSheet.get_Range("A1").Select();

}

activeWorkbook.Application.CutCopyMode = ExcelAPI.XlCutCopyMode.xlCopy;

activeWorkbook.Application.DisplayAlerts = true;

Some sheets are copied without any exception but for some of the sheets "You can't paste this here because the Copy area and paste area aren't the same size. Select just one cell in the paste area or an area that's the same size, and try pasting again. " exception occurs when wkSheet.Cells.PasteSpecial(ExcelAPI.XlPasteType.xlPasteValues, ExcelAPI.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); is called. Could you please help us with the reason behind this exception and suggest solutions to resolve it.

Answer 1

This error occurs when the copy area and paste area are not the same size. In your code, you are copying the entire worksheet and pasting it into a new workbook. This can cause issues if the new workbook has a different number of rows or columns than the original worksheet.

To resolve this issue, you can try selecting a specific range to copy and paste instead of copying the entire worksheet. You can also try adjusting the size of the paste area to match the size of the copy area.

Here's an example of how to copy and paste a specific range:

=================================

wkSheet.Range("A1:AH84").Copy();

newWorkbook.Worksheets[1].Range("A1:AH84").PasteSpecial(ExcelAPI.XlPasteType.xlPasteValues, ExcelAPI.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);

=================================

In this example, we are copying the range A1:AH84 from the original worksheet and pasting it into the first worksheet of the new workbook. You can adjust the range to match the size of your data.