VBA - Excel 从不同位置的不同工作表获取相同数据

The basic idea (beside looping through your files):

1. Change your lookup data into the following:

   

2. Then read Cells(1, 6) to get your model.

   Dim Model As String
   Model = Worksheets("MyTemplate").Cells(1, 6).Value
   

3. Use the WorksheetFunction.Match method to find your field in the lookup table.

   Dim FieldRow As Long
   FieldRow = Application.WorksheetFunction.Match(Model & "-First name", Worksheets("LookupTable").Range("A:A"), 0)
   

4. Use …

   fRow = Worksheets("LookupTable").Cells(FieldRow, 2)
   fColumn = Worksheets("LookupTable").Cells(FieldRow, 3)
   

   To get row and column where to look for that field in your template.

If you put the field lookup stuff into a handy function, the code would get easier to maintain. For example put the following into a module:

Option Explicit

Public LookupCache As Variant
Public LookupResults As Variant

Public Function ReadField(Ws As Worksheet, FieldName As String) As Variant
    'Here we cache the lookup table. It reads the sheet LookupTable into an 
    'array if the array does not exist yet. If the function runs a second time,
    'the array exists already and is used directly (saves time).
    'Lookup in arrays is much faster than in cells.
    'Caching makes this function about 2 times faster than without.
    If IsEmpty(LookupCache) Or IsEmpty(LookupResults) Then
        With ThisWorkbook.Worksheets("LookupTable")
            Dim LastLookupRow As Long
            LastLookupRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            LookupCache = .Range("A2", "A" & LastLookupRow).Value
            LookupResults = .Range("B2", "C" & LastLookupRow).Value
        End With
    End If

    Dim ModelName As String
    ModelName = Ws.Cells(1, 6).Value

    Dim LookupRow As Long
    On Error Resume Next
    LookupRow = Application.WorksheetFunction.Match(ModelName & "-" & FieldName, LookupCache, 0)
    On Error GoTo 0

    If LookupRow = 0 Then
        'field not found
        ReadField = CVErr(xlErrNA)
        Exit Function
    End If

    Dim fRow As Long, fColumn As Long
    fRow = LookupResults(LookupRow, 1)
    fColumn = LookupResults(LookupRow, 2)

    ReadField = Ws.Cells(fRow, fColumn).Value
End Function

So you could read a field like

Debug.Print ReadField(MyLoopWorkbook.Worksheets("MyTemplate"), "First name")
'MyLoopWorkbook should be the current workbook in your files loop

Edit according to the comment …

If we added a new field Company to a new model4,a user must go to the sheet lookuptable and add Model4-Company at line 11 with the row and col, but also in the code he has to go and add ReadField(MyLoopWorkbook.Worksheets("MyNewTemplate"), "Company"), right? That's why I am not understanding how I can count only on persons who don't code to add that? Can you clarify please because what you said is really important.

If you make the ReadField part dynamic you don't need to code here too. For example if you want to end up with a table like that:

您只需在第 4 列中添加一个新标题，称为字段 eg Company。并编写一个循环，循环遍历该标题行的列以收集所有字段。

Sub ReadAllFields()
    Dim wsData As Worksheet
    Set wsData = Worksheets("CollectedData")

    Dim FreeRow As Long 'find next free row in table
    FreeRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row + 1

    Dim Fields() As Variant 'read headers into array
    Fields = wsData.Range("A1", wsData.Cells(1, wsData.Columns.Count).End(xlToLeft)).Value

    Dim iCol As Long
    For iCol = 1 To UBound(Fields, 2) 'loop through header columns
        wsData.Cells(FreeRow, iCol).Value = ReadField(MyLoopWorkbook.Worksheets("MyNewTemplate"), Fields(1, iCol)) 
        'reads fields dynamically depending on which headers exist in data sheet
    Next iCol
End Sub