VBA code to copy worksheets works in Excel 2003 but has resource issue in later versions

Hi All,

Hoping you can help. I have a piece of code which I found on a forum years ago and have been using successfully in a number of projects. The code basically gets you to select a workbook, opens it, and copies the worksheets to the matching sheets in the original workbook. Eg, opens a workbook, copies contents of worksheet 'ABCD' from the new workbook to worksheet 'ABCD' in the original workbook. It works fine when opening workbooks in 2003 format. When I run the code and open a 2007 workbook, I get an Excel error with a big red cross saying:

* Excel cannot complete this task with available resources. Choose less data or close other applications.

Here is the code:

Please Login or Register  to view this content.

I'm gathering it has something to do with the greater number of lines in the newer version so I may be trying to copy hundreds of thousands of lines instead of tens of thousands as I did in 2003.

I'd appreciate any assistance in figuring out why this code no longer works with 2007/2010 Excel.

Many thanks

Trav

Sorry forgot one part of the code, at the very end:

Please Login or Register  to view this content.

Ok so I've worked out that the "SpecialCells(xlCellTypeLastCell)" is the part causing me grief. Apparently this is a bad way of finding the last cell. I think because the sheets that I'm wanting to import have formatting that extends beyond the actual data, it is trying to copy across the whole sheet instead of just the data. I have created a dummy sheet where I have deleted all rows and columns around the data, and it imports fine. The problem is that the report generates with the formatting and I don't want to have to manually go and edit it before importing the data.

As such, I think I need to find a different way of defining the range that I am copying from one workbook to the other.

Any help in rewording the code to do this would be greatly appreciated.

Cheers

Trav