VBA: How to prevent runtime scenarios when manipulating a large dataset with multiple worksheets?
I'm writing unit tests and I'm working on an Excel VBA project where I need to manipulate a large dataset that spans multiple worksheets. The dataset consists of over 50,000 rows across 10 different sheets, and Iām working with a runtime behavior (1004) when trying to copy data from one sheet to another. My goal is to consolidate the data from all sheets into a summary sheet. Below is the relevant code snippet that shows how I'm attempting to do this: ```vba Sub ConsolidateData() Dim ws As Worksheet Dim summarySheet As Worksheet Dim lastRow As Long Dim summaryRow As Long ' Create a new summary sheet Set summarySheet = ThisWorkbook.Worksheets.Add summaryRow = 1 ' Loop through each worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> summarySheet.Name Then lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ws.Range("A1:B" & lastRow).Copy summarySheet.Cells(summaryRow, 1) summaryRow = summaryRow + lastRow End If Next ws End Sub ``` When executing this code, I receive the following behavior message: "Run-time behavior '1004': Application-defined or object-defined behavior." I suspect that this could be due to either the size of the range I'm trying to copy or issues related to screen updating or application settings. I've tried adding `Application.ScreenUpdating = False` at the start of the subroutine and `Application.ScreenUpdating = True` at the end, but the behavior still continues. Additionally, I've ensured that there's enough memory available in Excel and that the references to the sheets are correct. Is there a better way to handle large datasets in VBA to avoid this behavior? Any suggestions on best practices for copying data efficiently across multiple sheets would be greatly appreciated. I'm using Excel 2019 on Windows 10. For reference, this is a production web app. How would you solve this?