Excel VBA to Consolidate Data from Multiple Sheets into One - working with 'Subscript Out of Range' scenarios
This might be a silly question, but I tried several approaches but none seem to work. I'm trying to write a VBA script to consolidate data from multiple sheets into a single summary sheet in Excel. The goal is to loop through all sheets in the workbook, collect data from a specific range in each sheet, and append this data to a summary sheet. However, I'm working with a 'Subscript Out of Range' behavior when I attempt to reference the sheet names. Here’s the code I’ve written so far: ```vba Sub ConsolidateData() Dim ws As Worksheet Dim summarySheet As Worksheet Dim summaryRow As Long ' 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 summaryRow = 1 ' Loop through each worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Summary" Then ' Assuming data is in A1:D10 of each sheet ws.Range("A1:D10").Copy summarySheet.Cells(summaryRow, 1).PasteSpecial xlPasteValues summaryRow = summaryRow + 10 End If Next ws Application.CutCopyMode = False MsgBox "Data consolidation complete!" End Sub ``` The behavior occurs at the line where I try to set `summarySheet`. I’ve double-checked the spelling of my sheet names and confirmed that the workbook has multiple sheets. I’ve also ensured that the "Summary" sheet does not exist at the start of the script. I’m running Excel 2019, and I suspect that there might be an scenario with the behavior handling or the way I’m referencing sheets. I would appreciate any insights into why this could be happening and how I might resolve it. I’ve also tried using `ThisWorkbook.Sheets.Count` to verify the number of sheets, and it returns the correct count, but I still get the behavior when referencing the sheet names. Thank you for your help! Any ideas what could be causing this? I'm working on a CLI tool that needs to handle this. How would you solve this? Any ideas what could be causing this?