VBA Macros to Combine Data from Multiple Sheets - Issues with Non-Uniform Ranges
I'm dealing with I've hit a wall trying to I'm trying to create a VBA macro that combines data from multiple sheets into one summary sheet in Excel 2021. The challenge I'm encountering is that each source sheet has a different number of rows and some columns may not align perfectly. I want to ensure that all unique column headers are maintained in the summary sheet while also capturing the data without losing any information. I've written the following code snippet to loop through each sheet and copy the data: ```vba Sub CombineData() Dim ws As Worksheet Dim summarySheet As Worksheet Dim lastRow As Long Dim summaryRow As Long Dim headerDict As Object Set headerDict = CreateObject("Scripting.Dictionary") summaryRow = 1 ' Create summary sheet Set summarySheet = ThisWorkbook.Worksheets.Add summarySheet.Name = "Summary" ' Loop through all sheets For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Summary" Then lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' Assuming data starts from row 1 For i = 1 To lastRow Dim j As Integer For j = 1 To ws.Cells(i, ws.Columns.Count).End(xlToLeft).Column Dim header As String header = ws.Cells(1, j).Value If Not headerDict.Exists(header) Then headerDict.Add header, summaryRow summarySheet.Cells(1, summaryRow).Value = header summaryRow = summaryRow + 1 End If summarySheet.Cells(i + 1, headerDict(header)).Value = ws.Cells(i, j).Value Next j Next i End If Next ws End Sub ``` However, when I run this code, I get inconsistent data in the summary sheet. If a column header exists in one sheet but not in another, it seems to leave blank cells without properly consolidating that data. I've also noticed that if two sheets have the same header at different positions, the data does not line up correctly, resulting in data misalignment. I've tried using `Union` to merge ranges, but it doesn't factor in the varying row counts well and leads to further complications. Any advice on how to properly handle these non-uniform ranges and ensure that the data aligns correctly would be greatly appreciated! Also, what are the best practices for dealing with different structures across sheets in this sort of task? My team is using Vba for this REST API. Thanks for any help you can provide! I'm working in a Windows 10 environment. Am I approaching this the right way?