I have a large number of data files split into two sets of files that I would like to consolidate into a single file. The target consolidated data looks like this:

target data consolidation table, where each colour either comes from a different data file or needs to be inserted in the code (like the row labels and average formula).

blue block of data comes from here, omitting column BD

green block of data comes from here. Title ideally comes from here too, highlighted in pink

My code so far is below.

Sub ConsolidateData()
    Dim mainWB As Workbook
    Dim wsMaster As Worksheet
    Dim folderPath1 As String
    Dim folderPath2 As String
    Dim sourceWB1 As Workbook
    Dim sourceWS1 As Worksheet
    Dim lastCol As Long
    Dim lastRow As Long
    Dim filename1 As String
    Dim filename2 As String
    Dim i As Integer
    
         
    ' Set your master workbook
    Set mainWB = ThisWorkbook
    
    ' Set the master "Data" worksheet
    Set wsMaster = mainWB.Sheets("Data")
    
    ' Specify the folder path where your source workbooks are located
    folderPath1 = "U:Macro for Gloss-O-MetreXLSX from CSV" 'L, a, b, C, DE located here
    folderPath2 = "U:Macro for Gloss-O-MetreXLSX from QTX" 'gloss values located here
    
    ' Check if the folder paths ends with a backslash, if not, add it
    If Right(folderPath1, 1) <> "" Then
        folderPath1 = folderPath1 & ""
    End If
    
     If Right(folderPath2, 1) <> "" Then
        folderPath2 = folderPath2 & ""
    End If
    
    ' Get the first file in the folder from path1
    filename1 = Dir(folderPath1 & "*.xlsx")
    
  
    ' Loop through all files in the folder
    Do While filename1 <> ""
        ' Open the workbook
        Set sourceWB1 = Workbooks.Open(folderPath1 & filename1)
        ' Set the source worksheet and access the first sheet
        Set sourceWS1 = sourceWB1.Sheets(1)
                
        ' Copy L, a, b, C values and headings from source worksheet
        sourceWS1.Range("AZ3:BC8, BE3:BE8").Copy 'omit Cells BD3:BD7
        
        ' Find the last row in the master "Data" sheet
        lastCol = wsMaster.Cells(wsMaster.Columns.Count).End(xlToLeft).Column
        
        'Here I want to insert the row headings to the left of the first column (i.e., 3 spaces right of last column to be 1 column behind the next set of data at 4 spaces right of lastCol)
        ws.Master.Cells(lastCol + 3).Value = "L" & "a" & "b" & "C" & "Gloss"
        
        ' Paste into the master "Data" worksheet starting from last column plus 4 spaces
        wsMaster.Cells(lastCol + 4).PasteSpecial Transpose:=True
       
        'insert average of the pasted data in each row (from last column+1 row 1 to row 8)
        Range(lastCol + 1 & "1:" & lastCol + 1 & "8").Formula = "=""average(" & lastCol - 5 & "1:" & lastCol & "1)"
        
        Application.CutCopyMode = False ' Clear clipboard
        
        sourceWB1.Close SaveChanges:=False ' Close the source workbook without saving
        
        filename1 = Dir ' Move to the next file in the folder
    Loop
    
    'get the first file in the folder from path2
    filename2 = Dir(folderPath2 & "*.xlsx")
    'i = 0
    
    Do While filename2 <> ""
        ' Open the workbook
        Set sourceWB2 = Workbooks.Open(folderPath2 & filename2)
        
        ' Set the source worksheet and access the first sheet
        Set sourceWS2 = sourceWB2.Sheets(1)
        
        ' Copy gloss values and headings from source worksheet
        sourceWS2.Range("B9, B20, B31, B42, B53, B64").Copy
        
        'Find the last row and last column of the previously pasted data set - my current code is not going to "triangulate" the location
        lastRow = wsMaster.Cells(wsMaster.Rows.Count).End(xlUp).Row
        lastCol = wsMaster.Cells(wsMaster.Columns.Count).End(xlToLeft).Column
        
        'Paste the values in the bottom left of the data set to fill final row
        wsMaster.Cells(lastRow + 2 & lastCol - 5).PasteSpecial Transpose:=True
        
        Application.CutCopyMode = False
        sourceWB2.Close SaveChanges:=False
        filename2 = Dir
        'i = i + 10
    Loop
        
        
End Sub

I had it working to copy the data for folderPath1, but once I tried to add the averages and insert the =average() formula, I got failures. When I comment those failed lines out, the first while loop works. The second while loop copy and paste works, but when I try to paste the green data to the specific location based on lastCol and last Row, the code fails. If I remove those and try to paste the data from multiple files in folderPath2, the code does not loop (but does not fail, either).

I currently don’t have the titles being inserted (pink cells and grey cells).

I appreciate any help on this problem!