Out of resources error when copy/paste in 2007 VBA

[Sparkle99]

Looks like innocuous code to me - and it works fine and dandy in Excel 2003...

Sheet1.Columns("A:" & lastcol).Copy
Sheet2.Columns("A:" & lastcol).PasteSpecial xlPasteValuesAndNumberFormats, SkipBlanks:=True

Where lastcol has been set at 'AZ' or similar
This is a really small sheet being copied (<20 rows) but there is an auto-filter reducing the lines down to 5 or so.

In 2007, I get an 'Out of resources' error.

Any ideas why - or better still, a simple way of achieving the same end.

Cheers for any advice given.

 

[Jerry Sullivan]

Hello Sparkle99,

You will probably have better results if you limit the range being copied to those with data. You can try:

Sub Copy_DataCells_Only()
    Dim lngLastRow As Long, lngLastCol As Long
    Application.ScreenUpdating = False
    With Sheet1
        lngLastRow = .Cells.Find(What:="*", After:=.Range("A1"), _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
        lngLastCol = .Cells.Find(What:="*", After:=.Range("A1"), _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        .Range("A1", .Cells(lngLastRow, lngLastCol)).Copy
    End With
    Sheet2.Range("A1").PasteSpecial _
        Paste:=xlPasteFormulasAndNumberFormats, _
        SkipBlanks:=True
End Sub

When you tried using the equivalent of this statement:

Sheet1.Columns("A:AZ").Copy

in xl2007, that copies over 52 Million cells (52 col x 1,048,576 rows)
in xl2003, worksheets only have 65,536 rows which explains why this might have worked for you previously.