+ Reply to Thread
Results 1 to 2 of 2

Issue copying and pasting large data from separate workbooks into a new workbook

  1. #1
    Registered User
    Join Date
    04-26-2011
    Location
    Baltimore, MD
    MS-Off Ver
    Excel 2007
    Posts
    5

    Issue copying and pasting large data from separate workbooks into a new workbook

    Hi,
    I am trying to copy blocks of data from separate workbooks (each block 30,000 to 60,000 rows by 9 columns) and append in one summary workbook. The first file is processed correctly with no issues, but on the second file, I receive an error "Run time error '1004': Application-defined or object-defined error"

    simplified extract of code follows:
    Please Login or Register  to view this content.
    for file1, lastrow (of the data file) = 635,701 and numrows (of filtered data) = 43,032
    for file 2, lastrow (of the data file) = 798,851 and numrows (of filtered data)=54,076
    The data from file 2 should be pasted starting with row 43,033 but it stops at the line where the KeyArr is written back to the range in new_book.

    I thought that using the array would prevent it, but am I hitting the row limit on the pasting process?
    The problem is, I could have up to 3 more workbooks to process, each with another ~40,000 rows to append. I only need values; formats are not critical.

    Any ideas?

    Thanks in advance,
    Dave
    Last edited by davo52; 12-11-2014 at 10:04 AM.

  2. #2
    Registered User
    Join Date
    04-26-2011
    Location
    Baltimore, MD
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Issue copying and pasting large data from separate workbooks into a new workbook

    I think I solved this.
    I am pasting into a new workbook (generated with Workbooks.Add) that by default was in COMPATIBILITY MODE. I did more research and found examples of where that situation created conflict on pasting more than 65,000 rows. I used Application.DefaultSaveFormat = 51 to set the format to .xlsx before I add the new workbook, then return the DefaultSaveFormat to its prior setting afterward. After limited testing, this seems to work -- I am not getting the error that blocked me previously. I was also able to remove the use of the array and just paste into the target address.

    Maybe this will help someone else.

    Regards,
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copying data from other workbooks and pasting into master workbook
    By jcook1100 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-15-2013, 08:03 AM
  2. [SOLVED] Help: Splitting large workbook into separate workbooks based on R column value.
    By LightingPop in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-20-2013, 11:17 AM
  3. [SOLVED] Macro for Copying and pasting from one excel workbook into a series of other workbooks
    By Smeddlesboy in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 01-09-2013, 02:12 PM
  4. Replies: 1
    Last Post: 11-21-2012, 11:23 AM
  5. Replies: 1
    Last Post: 07-20-2012, 06:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1