PDA

View Full Version : Moving to Excel 2010 - worksheets.move compatibility



AdrianK
07-08-2011, 09:26 AM
Hello All,

I'm hoping you can help me with this as it's currently driving me mad.

The company I work for is beginning a staged changeover from Excel 2003 (<3) to Excel 2010 (:|) sadly as I have dozens of macros which move sheets to various workbooks this is causing serious problems.

I understand that with 1+million rows as opposed to 65,536 where the "Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns.." error is coming from, but am looking for a simple and efficient workaround.

As soon as CSV files open in 2010 they immediately pick up the million (empty) rows. When I then try and move them to a 2003 document then i get the error and the macro crashes.

I appreciate that I can .saveas, .close, .open the file and then move it or copy the data, start a new worksheet and paste the data, but am hoping there is another way as these seem like they are such convoluted ways to solve this problem.

In certain cases I have 20+ files that are opened and then sheets moved to a master (which needs to be in 2003 format as not everyone has migrated yet) and the lovely .move function loveingly closes these when moving the sheet.

As I say I have dozens of macros that I will need to "upgrade" with this clunky and inelegant "solution".

Is there any way that someone might know of where an efficient method can be had to carry on using the sensible (i.e. worksheet.move/copy) solution?

Seems to me it's just one of so many things that MS have done that actually hamper rather than help people (don't get me started on the ribbon!!)

Plus I personally believe that when you're analysing that volume of data (more than 65536 rows/256 columns) that Access (or some other Db package) is the right tool for the job in most situations.

Any help greatly appreciated.

Many thanks,

Adrian