I've posted a similar question here years ago and it was answered. Funny thing is that it's the same issue, but using the solution that was provided way back then and the amount of data is much more.
Basically I have a "raw data" tab that contains rows of data and I'm copying a template to another tab that puts selected columns from that raw tab into an easy to read format. I've never had this much data to process before. Excel 2007 XLSB (binary) file I'm sure can handle it, I'll only use about 110K rows when it's done, but it's dying at row 7,946 each time on item 568. The error it throws states that the copy range method has failed. I've gone over the modules associated with it to ensure minimal overhead is being used. Have I hit a wall?
Ctrl-M launches the macro which wipes the report tabs and reinitiates each report. It dies on following the initial building of the detail tab where it is adding rows (add_detail_rows) which is initially called by copy_detail_template.