Results 1 to 2 of 2

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

Threaded View

  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:
    Dim fname as string
    Dim numrows as double, rowmark as double, lastrow as double
    fc_book as workbook, control_book as workbook
    Dim KeyArr as variant
    '
    set new_book =Workbooks.Add
    rowmark=1
    For x = 1 to numfiles
         fname=control_book.sheets("Sheet1").range("AM"& 50+x)
         Workbooks.open (fname)
         set fc_book=Activeworkbook
    '    get # rows of data based upon filter of revdate
         numrows=countif(fc_book.sheets(data_sht).range("E2:E" & lastrow), revdate)
    '    copy  the filtered data to a new sheet (same file) to separate it.
         rngdata.copy Destination:=fc_book.sheets("Sheet2").range("A1:H" & lastrow)
        'copy the filtered data to a new book
        Keyrr=fc_book.sheets("Sheet2").range(("A2:H"& numrows+1).value
        new_book.sheets("Sheet1").Range("A" & rowmark+1 & ":H" & rowmark + numrows) = KeyArr
        erase KeyArr
        rowmark=rowmark+numrows
        fc_book. close savechanges:=false
        set fc_book = nothing
    Next x
    'other code
    'more code
    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.

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