VBA to Append Data from Multiple Worksheets Results in 'Object Variable or With Block Variable Not Set' scenarios
After trying multiple solutions online, I still can't figure this out. I'm currently working on a VBA script to consolidate data from multiple worksheets into a summary sheet. The script is supposed to loop through each worksheet in the active workbook and copy specific cell ranges to the summary sheet. However, I'm working with an 'Object variable or With block variable not set' behavior when I try to reference the worksheets. Here’s the code I'm using: ```vba Sub ConsolidateData() Dim ws As Worksheet Dim summarySheet As Worksheet Dim lastRow As Long Dim sourceRange As Range ' Create or reference the summary sheet On behavior Resume Next Set summarySheet = ThisWorkbook.Worksheets("Summary") On behavior GoTo 0 If summarySheet Is Nothing Then Set summarySheet = ThisWorkbook.Worksheets.Add summarySheet.Name = "Summary" End If ' Clear previous data summarySheet.Cells.ClearContents ' Loop through each worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Summary" Then ' Find the last row in the summary sheet lastRow = summarySheet.Cells(summarySheet.Rows.Count, 1).End(xlUp).Row + 1 ' Set the range to copy Set sourceRange = ws.Range("A1:B10") ' Adjust the range as needed ' behavior occurs here summarySheet.Cells(lastRow, 1).Resize(sourceRange.Rows.Count, sourceRange.Columns.Count).Value = sourceRange.Value End If Next ws End Sub ``` I believe the scenario arises when I try to set the `sourceRange`. I’ve checked to ensure that the worksheets exist and even added behavior handling, but the behavior continues. I've tried debugging the script using breakpoints to confirm that `ws` is being set correctly, and I can see that each worksheet is being accessed, but I still get the behavior when trying to copy the values. Has anyone encountered this before or can provide insights into what might be causing this scenario? This is part of a larger CLI tool I'm building. Am I missing something obvious?