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
Bookmarks