The information cannot be pasted because the copy and paste areas are not the same size and shape

1021557 · May 21, 2012

Hi, Can anyone help me with this message in Excel 2010. Basically I have a sheet that has multiple values in it. I have a vlookup formaula that references a sheet in the same workbook. When I use autofilter to select certain values and paste special (e.g. the filter displays values in cells D1556, 1560 and 1562 so those in between are hidden)-values I get the error message:
'The information cannot be pasted because the copy and paste areas are not the same size and shape'. I think this was a bug in previous Excel versions and I think a workaround was to open the file disable aotifilter and save and then re-open but this hasn't worked for me. I've also tried selecting visible cells only but then I get the message 'The command cannot be used on multiple selections'.

Is there any workaround?

Thanks

Jerry Sullivan · May 21, 2012

Hi,

What range do you have selected when you Copy (including Cells in Rows Hidden by Autofilter), and what range do you have selected when you try to PasteSpecial?

1021557 · May 22, 2012

I just the selected visible cells

Jerry Sullivan · May 22, 2012

I was asking for an example of the specific range address you're copying from and pasting too, because that can affect the result.

As one example, if you try to copy the visible cells in Range (1:100) and PasteSpecial to Cell B200, you'll get the error message you describe.

1021557 · May 22, 2012

Hi, It's the same range so if I select cells D1:D100 I'm copying and then doing paste special-values to that range, naturally with the filter on not all of the values in D1:100 will be visible, if they were then I wouldn't be having this problem.

Jerry Sullivan · May 22, 2012

Now I understand. The problem is when you Paste a multiple-cell range from the clipboard into Filtered Rows (regardless of the source you copied), Excel tries to Paste into all the cells including the hidden cells.

You could try a macro like this....

Code:

Sub VisibleSelection_PasteAsValues()
    Dim c As Range
    For Each c In Selection
        c.Value = c.Value
    Next
End Sub

There might be an easy, non-VBA way to do this.
At the moment, I'm not able to think of any simple methods.

Jerry Sullivan · May 22, 2012

This would be better. My previous suggestion would require that you've already qualified the Selection as only Visible Cells.

Code:

Sub VisibleSelection_PasteAsValues()
    Dim c As Range
    For Each c In Selection.SpecialCells(xlCellTypeVisible)
        c.Value = c.Value
    Next
End Sub

The information cannot be pasted because the copy and paste areas are not the same size and shape

1021557

Board Regular

Excel Facts

Jerry Sullivan

MrExcel MVP

1021557

Board Regular

Jerry Sullivan

MrExcel MVP

1021557

Board Regular

Jerry Sullivan

MrExcel MVP

Jerry Sullivan

MrExcel MVP

Similar threads

Forum statistics

Share this page

The information cannot be pasted because the copy and paste areas are not the same size and shape

1021557

Board Regular

Excel Facts

Jerry Sullivan

MrExcel MVP

1021557

Board Regular

Jerry Sullivan

MrExcel MVP

1021557

Board Regular

Jerry Sullivan

MrExcel MVP

Jerry Sullivan

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock