ipmh97
07-31-2021, 09:42 PM
Hi, I need to access different sheets across different workbooks and consolidate the data into one consolidate file.
I'm thinking of just using the Power Query function on excel to do the consolidation. However, one issue I foresee before joining using power query is that some of the sheets have missing columns. I have gotten a vba to insert any missing columns in their appropriate position. However, I'm not sure of how to edit the code to make it loop through the different workbooks and sheets. I only need the code to run through specific sheets and specific workbooks (ie Only check sheets with "New" or "Continue" in their sheet titles and only check the excel files with "Report" in their names). Thank you for the help!
Sub AddMissingHeader()
Dim headers() As Variant
headers = Array("Report_Date", "Company", "Customer_Id", "Product_Id", "Company_Name")
Dim i As Long
For i = LBound(headers) To UBound(headers)
If Cells(5, i + 1).Value <> headers(i) Then
Columns(i + 1).EntireColumn.Insert
Cells(5, i + 1).Value = headers(i)
End If
Next i
End Sub
I'm thinking of just using the Power Query function on excel to do the consolidation. However, one issue I foresee before joining using power query is that some of the sheets have missing columns. I have gotten a vba to insert any missing columns in their appropriate position. However, I'm not sure of how to edit the code to make it loop through the different workbooks and sheets. I only need the code to run through specific sheets and specific workbooks (ie Only check sheets with "New" or "Continue" in their sheet titles and only check the excel files with "Report" in their names). Thank you for the help!
Sub AddMissingHeader()
Dim headers() As Variant
headers = Array("Report_Date", "Company", "Customer_Id", "Product_Id", "Company_Name")
Dim i As Long
For i = LBound(headers) To UBound(headers)
If Cells(5, i + 1).Value <> headers(i) Then
Columns(i + 1).EntireColumn.Insert
Cells(5, i + 1).Value = headers(i)
End If
Next i
End Sub