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

Question

I have an excel macro that prepares a report. The macro is used daily but has thrown up the above error today. The error occurs at the final line of the given code. Have people experienced this issue? The second table which the macro draws from (and picks up the error) has gotten quite large, would this be the cause of the issue?

    Windows("Worldwide_Backlog.xlsm").Activate 'Save File as Worldwide_Backlog'
    Worksheets(3).Activate
        ActiveSheet.PivotTables("PivotTable1").PivotFields("SubFamily2"). _
         ClearAllFilters
        ActiveSheet.PivotTables("PivotTable1").PivotFields("SubFamily2").CurrentPage = _
         "DAS"
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Product Family"). _
          ClearAllFilters
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Product Family"). _
          CurrentPage = "REMI"

    'Clear Filters'

             ActiveSheet.PivotTables("PivotTable1").PivotFields("Age").ClearAllFilters



    'Copy and Paste Table into new Workbook'
              Range("A9").Select
              Selection.End(xlToRight).Select
              Selection.End(xlToLeft).Select
              Range(Selection, Selection.End(xlToRight)).Select
              Range(Selection, Selection.End(xlDown)).Select
              Selection.Copy
              Workbooks.Add
              ActiveSheet.Paste

     'Delete Grand Total from newly pasted table'

         Dim Firstrow As Long
         Dim Lastrow As Long
         Dim Lrow As Long
         Dim CalcMode As Long
         Dim ViewMode As Long

    With Application
         CalcMode = .Calculation
         .Calculation = xlCalculationManual
         .ScreenUpdating = False
    End With


        With ActiveSheet


         .Select


    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView


    .DisplayPageBreaks = False

    Firstrow = .UsedRange.Cells(1).Row
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row


    For Lrow = Lastrow To Firstrow Step -1


        With .Cells(Lrow, "A")

            If Not IsError(.Value) Then

                If .Value = "Grand Total" Then .EntireRow.Delete


            End If

        End With

    Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
    .ScreenUpdating = True
    .Calculation = CalcMode
End With



         Windows("Worldwide_Backlog.xlsm").Activate
         Worksheets(3).Activate
         ActiveSheet.PivotTables("PivotTable1").PivotFields("SubFamily2"). _
         ClearAllFilters
         ActiveSheet.PivotTables("PivotTable1").PivotFields("SubFamily2").CurrentPage = _
         "Ra"
         Range("A11").Select
         Application.CutCopyMode = False

         ActiveSheet.PivotTables("PivotTable1").PivotSelect "Order Number", xlButton, _
         True
         Range(Selection, Selection.End(xlToRight)).Select
         Range(Selection, Selection.End(xlDown)).Select
         Selection.Copy
         Workbooks.Add
         ActiveSheet.Paste




        'Delete Grand Total'

With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With


With ActiveSheet


    .Select


    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView


    .DisplayPageBreaks = False


    Firstrow = .UsedRange.Cells(1).Row
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row


    For Lrow = Lastrow To Firstrow Step -1


        With .Cells(Lrow, "A")

            If Not IsError(.Value) Then

                If .Value = "Grand Total" Then .EntireRow.Delete


            End If

        End With

    Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
    .ScreenUpdating = True
    .Calculation = CalcMode
End With


          Range("M1").Select
             ActiveCell.FormulaR1C1 = "Vlookup"
             Range("M2").Select
             ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-12],[Book1]Sheet1!C1,1,0)"
             Range("L2").Select 'AutoFills'
                Selection.End(xlDown).Select
                ActiveCell.Offset(0, 1).Select
                Range(Selection, Selection.End(xlUp)).Select
                Selection.FillDown


             Columns("M:M").Select
             Selection.AutoFilter
             ActiveSheet.Range("$M$1:$M$21").AutoFilter Field:=1, Criteria1:="=#N/A", _
             Operator:=xlOr, Criteria2:="="
             Range("A2").Select
             Range(Selection, Selection.End(xlToRight)).Select
             Range(Selection, Selection.End(xlDown)).Select
             Selection.Copy
             Windows("Book1").Activate
             ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1).Select 'Uses first blank cell'
             ActiveSheet.Paste

[Starting point](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Siddharth Rout, Jun 09 '15 at 09:50
Make sure that the select before the paste actually selects one cell. Set a breakpoint after this select and then inspect the sheet; the selection will be visible. — Paul Ogilvie, Jun 09 '15 at 11:31

score 0 · Answer 1 · answered Jan 31 '18 at 10:31

The sheet data is badly formatted. There are some rows in the sheet which has some columns merged into one, thereby reducing the number of columns for those rows. That is why its complaining about size difference.

Copy the data to notepad++ and check the formatting issue.

score -2 · Answer 2 · answered Feb 01 '18 at 12:36

-2

The destination file is on compatibility mode. Go to File > Info Convert > Save. That will solve your issue.

answered Feb 01 '18 at 12:36

snnpro

193
2

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

2 Answers2