Excel 2010 Not able to run my Macro

We have a number of Macros that were built using Excel 2003 (and ran fine) that are now not able to run using Excel 2010. I'm guessing it is some type of syntax issue, but I haven't been able to find anything specific to this issue online, so that's why I'm here.

I am receiving the following error on the "Selection.Entirerow.Insert" line:

"Run-time error '1004': The information cannot be pasted because the Copy area and the paste area are not the same size and shape."

Essentially we are copying rows from one spreadsheet to another based on certain criteria.

Dim SiteCol As Range, Cell As Object
Set SiteCol = Range("B1:B65536")
For Each Cell In SiteCol
    If Cell.Value Like "MyValue*" Then
    Selection.EntireRow.Copy
    Windows("MacroWorkbook.xls").Activate
    Sheets("Import File").Select
    Range("A65536").End(xlUp).Offset(1, 0).Select
    Selection.EntireRow.Insert
    Application.CutCopyMode = False
    End If
Next

Any help is much appreciated!

Doug Walker

Maybe try this revised version (not tested):

Dim SiteCol As Range, Cell As Object
Set SiteCol = Range("B1:B" & Range("B" & Rows. Count).End(xlUp).Row)
For Each Cell In SiteCol
    If Cell.Value Like "MyValue*" Then
        Cell.EntireRow.Copy
        Windows("MacroWorkbook.xls").Activate
        Sheets("Import File").Select
        Range("A"& Rows.Count).End(xlUp).Offset(1, 0).Paste
    End If
Next

Knew I was going to have to test it.

Shortened (working version):

Sub test()

Dim SiteCol As Range, Cell As Object
Set SiteCol = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
For Each Cell In SiteCol
    If Cell.Value Like "MyValue*" Then
        Cell.EntireRow.Copy
        Windows("MacroWorkbook.xls").Activate
        Sheets("Import File").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
    End If
Next

End Sub

Works like a charm. Much appreciated. A few questions in response if you don't mind.

Care to explain why my previous code broke? Was it just a syntax change through the versions as I wondered?

Also, I did add in one line to the If statement within the For.

Application.CutCopyMode = False

We have a large amount of data, and otherwise the user receives a message asking them to clear the clipboard.

Thanks Again!

I don't really know that I have not used Excel 03 for a while now, and even then I wasn't using vba. It might be that this line:

Selection.EntireRow.Insert

was trying to add more lines that the worksheet has. In my adjusted code, I just pasted instead of inserting lines.

Also instead of looping through all the rows in the sheet, I limited it to only the first cells that have information in Column B.

Hope this helps.

Good luck.

abousetta