Traverse matrix and loop help

Where do you get the 1200, 600, 800... etc. values? There was nothing in the workbook....

I see the labels and column headers, but everything else is blank. So, I'm not sure what you want done with those...

What would you want entered into cells if two or more files have the same cell filled in but with different values?

Instead of the format that you are suggesting, it might be better to create a database from only the filled values (Law Firm Name, Row label, column label, value, and other information if needed) that could then be filtered to show a value of interest.

Try the macro below - copy the code into a standard codemodule in an otherwise blank workbook, and run the macro "GetDataFromBidBook" and choose one of your bid files when prompted. Then try it a second time, choosing a different workbook. If the code works, it can be modified to allow the selection of all your files in one go instead of one at a time.

The code creates a database that can be filtered by county, to show the different values bid for that county. The second column can be filtered using the "Contains" text option to look for a specific service.

Sub GetDataFromBidBook()
    Dim rngD As Range
    Dim rngC As Range
    Dim rngV As Range
    Dim wb As Workbook
    Dim wsD As Worksheet
    Dim wsDB As Worksheet
    Dim lngR As Long
    
    Set wsDB = ThisWorkbook.Worksheets(1)
    wsDB.Cells(1, 1).Value = "County"
    wsDB.Cells(1, 2).Value = "Description"
    wsDB.Cells(1, 3).Value = "Amount"
    
    Set wb = Workbooks.Open(Application.GetOpenFilename("Bid Files (*.xlsx),*.xlsx", Title:="Choose a file to process"))
    For Each wsD In wb.Worksheets
        Set rngD = wsD.Range("A:A")
        For Each rngC In rngD.SpecialCells(xlCellTypeConstants)
            If Application.CountA(rngC.EntireRow) > 3 Then
                For Each rngV In rngC.Offset(0, 3).Resize(1, wsD.UsedRange.Columns.Count).SpecialCells(xlCellTypeConstants)
                    lngR = wsDB.Cells(wsDB.Rows.Count, 1).End(xlUp).Row + 1
                    wsDB.Cells(lngR, 1).Value = rngC.Value
                    wsDB.Cells(lngR, 2).Value = wsD.Cells(4, rngV.Column).Value
                    wsDB.Cells(lngR, 3).Value = rngV.Value
                Next rngV
            End If
        Next rngC
    Next wsD

    wsDB.UsedRange.EntireColumn.AutoFit
    
End Sub

The workbook that you posted the first time had only two sheets, and the workbook that you are using had six worksheets (three of which were hidden). And it had formulas in the range of cells where the users would enter their values - my code was looking for constants, not formulas. Macro code is specific enough that things like that matter. So, anyway, try this version, which looks for two "Pricing" sheets, and converts everything to values before proceeding (but does not over-write the original workbook, so it won't matter):

Sub GetDataFromBidBook()
    Dim rngD As Range
    Dim rngC As Range
    Dim rngV As Range
    Dim wb As Workbook
    Dim wsD As Worksheet
    Dim wsDB As Worksheet
    Dim lngR As Long
    
    Set wsDB = ThisWorkbook.Worksheets(1)
    wsDB.Cells(1, 1).Value = "County"
    wsDB.Cells(1, 2).Value = "Description"
    wsDB.Cells(1, 3).Value = "Amount"
    
    Set wb = Workbooks.Open(Application.GetOpenFilename("Bid Files (*.xlsx),*.xlsx", Title:="Choose a file to process"))
    For Each wsD In wb.Worksheets
        If wsD.Name Like "*Pricing" Then
            Set rngD = wsD.Range("A:A")
            wsD.UsedRange.Value = wsD.UsedRange.Value
            For Each rngC In Intersect(rngD, wsD.UsedRange)
                If rngC.Value <> "" Then
                    If Application.CountA(rngC.EntireRow) > 3 Then
                        For Each rngV In rngC.Offset(0, 3).Resize(1, wsD.UsedRange.Columns.Count).SpecialCells(xlCellTypeConstants)
                            lngR = wsDB.Cells(wsDB.Rows.Count, 1).End(xlUp).Row + 1
                            wsDB.Cells(lngR, 1).Value = rngC.Value
                            wsDB.Cells(lngR, 2).Value = wsD.Cells(4, rngV.Column).Value
                            wsDB.Cells(lngR, 3).Value = rngV.Value
                        Next rngV
                    End If
                End If
            Next rngC
        End If
    Next wsD
    
    wb.Close False
    
    wsDB.UsedRange.EntireColumn.AutoFit
    
End Sub

Great! Thanks for letting me know that my code worked for you.

I have no idea what you want at the end of all this: the range D8:W92 is completely filled, so why do you only want to fill column C from that range? Could you modify your input file to show what data you want in columns A to C based on your file Firm A.xlsx?

Try this in your input.xlsm. Select the file(s) of interest when prompted

Sub ProcessUserSelectedFiles()
    Dim FileArray As Variant
    Dim myBook As Workbook
    Dim shtFF As Worksheet
    Dim shtPT As Worksheet
    Dim shtFFP As Worksheet
    Dim shtPTP As Worksheet
    Dim lngC As Long
    Dim rngC As Range
    Dim i As Integer
    Dim c As Long
    
    Set shtFF = ThisWorkbook.Worksheets("Flat Fee")
    Set shtPT = ThisWorkbook.Worksheets("Phase Tier")
    
    FileArray = Application.GetOpenFilename(Title:="Select the files to process", MultiSelect:=True)
    
    If IsArray(FileArray) Then
        For i = LBound(FileArray) To UBound(FileArray)
            Workbooks.Open FileArray(i)
            Set myBook = ActiveWorkbook
            Set shtFFP = myBook.Worksheets("Flat Fee Pricing")
            With shtFF
               lngC = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
               .Cells(1, lngC).Value = shtFFP.Range("B2").Value
                If .Cells(.Rows.Count, "B").End(xlUp).Row < 10 Then
                    shtFFP.Range("A8:A79").Copy .Range("A2").Resize(20 * 72)
                    For Each rngC In shtFFP.Range("D4:W4")
                        rngC.Copy .Range("B2").Offset((rngC.Column - 4) * 72).Resize(72)
                    Next rngC
                End If
                For Each rngC In shtFFP.Range("D4:W4")
                    Intersect(rngC.EntireColumn, shtFFP.Range("8:79")).Copy .Cells(2, lngC).Offset((rngC.Column - 4) * 72).Resize(72)
                Next rngC
            End With
            Set shtPTP = myBook.Worksheets("Phase Tier Pricing")
            With shtPT
               lngC = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
               .Cells(1, lngC).Value = shtFFP.Range("B2").Value
                If .Cells(.Rows.Count, "B").End(xlUp).Row < 10 Then
                    shtPTP.Range("A9:A80").Copy .Range("A2").Resize(180 * 72)
                    c = 0
                    For Each rngC In shtPTP.Range("D4:GJ4")
                        If rngC.Value <> "" Then
                            c = c + 1
                            rngC.Copy .Range("B2").Offset((c - 1) * 72).Resize(72)
                        End If
                    Next rngC
                End If
                c = 0
                For Each rngC In shtPTP.Range("D4:GJ4")
                    If rngC.Value <> "" Then
                        c = c + 1
                        Intersect(rngC.EntireColumn, shtPTP.Range("9:80")).Copy .Cells(2, lngC).Offset((c - 1) * 72).Resize(72)
                    End If
                Next rngC
            End With
            myBook.Close False
        Next i
    End If
End Sub

You're welcome! Have fun transforming your data!