Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2008
    Location
    Kent, United Kingdom
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy > 65,536 rows into a new spreadsheet (Excel 2007)

    Hi

    I have a situation where our in house bespoke system outputs data into Excel. For audit purposes this data is read only (sheet protection). I need to be able to copy this data into a new spreadsheet (copy & paste value) so that I can manipulate it etc. This is fine when the row count is less than 65,536 rows but anymore than that and I get "The data can't be copied because the copy area & paste area aren't the same size".

    I've saved the output spreadsheet as all sorts of file types & changed the type under excel options as well but to no avail.

    Help please

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copy > 65,536 rows into a new spreadsheet (Excel 2007)

    Are you trying to copy into an older version of Excel? Before XL2007 the rowsize is limited to 65536 rows...

    Steve

  3. #3
    New Lounger
    Join Date
    Nov 2008
    Location
    Kent, United Kingdom
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy > 65,536 rows into a new spreadsheet (Excel 2007)

    The new workbook defaults to the old style but I have saved it as a new workbook / macro enabled workbook / binary workbook & also changed the excel options to be one of the new types.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy > 65,536 rows into a new spreadsheet (Excel 2007)

    You have to reload the new workbook to get it out of legacy mode.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    New Lounger
    Join Date
    Nov 2008
    Location
    Kent, United Kingdom
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy > 65,536 rows into a new spreadsheet (Excel 2007)

    That seems to have done the trick tyvm

    Is there a particular setting within Excel that is defaulting it to legacy mode?

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy > 65,536 rows into a new spreadsheet (Excel 2007)

    No setting, just open a file that is in the old xls format. The legacy setting only applies to that particular file.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •