VBA - Excel Getting same data from different sheets at different positions

Question

I want to make a macro that will open all excel books in a folder, read the filled information and store them in a sheet which will represent my database. I need to know your suggestions and what is the best way to do that, to get a fast and flexible result.

To help you understand my question, let us suppose that I have 3 excel templates containing First Name, Last Name and Country, but at different positions like these pictures

Template 1

Template 2

Template 3

Based on that, the final result that I would like to get is :

The exemple that I am giving by these pictures is really very simple, but it was just to help you understand what I want. Now I will detail about the real need. In fact, I have 3 templates, but each of them contains about 80 fields of data to collect (not only first name, last name and country). And i don't have to read only 3 files, but I have to read about 200 files placed in a folder and each of them is either template1, or 2 or 3. In the future we may have a template 4 that's why I need something flexible.

I thought about named ranges, but the template 1,2,3 already exists, and I can't collect from the 200 users the 200 existing excels files, and before launching my macro, giving a named range to the 80 field at each file. I can use named range if in the future they will be a template 4, so before sending the files to the final user who will fill the excel we name the ranges and send it to him, but before the template 4,i have to fix the problem of the current 3 existing templates.

I also thought about reading data based on columns and row indexes,for exemple I check the type of file and if I am reading a file template one, I get first name from the cell (2,3), and If it's a template 2, i get the information from cell (5,6) and if it's a template 3, i get the information from Cel (9,4), but the problem is that my code will not be at all flexible.

I also said, I may do like a sheet called reference, in which I define the positions of each field based on the template model, for example I say that the first name is for template 1 at the position 2,3 for the template 2, first name is at 5,6 and for template3 it's at 9,4. Like the following picture, and when I loop through my 200 files, I check, if it's template 1 i read the sheet of reference and I know that the first name will be at this position, same for template 2 and so on....this solution looks like previous one, but more flexible, because all we have to change is the reference table if something changes, but I am wondering if it will be fast or slow if for each field i have to come read 2 cells in the reference sheet to know the position.

I am really lost because I have to choose the best way to do what I want before start coding to avoid time wasting. If any expert can help by telling me what is best or giving me more ideas than what I thought about I will really appreciate.

Thanks in advance to any helper

EDIT: @PEH, what do you think about if I make my lookup table like that ?

EDIT2: @PEH, that's what is suggested in last comment

Answer 1

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:

You would just add a new header in column 4 called like the field eg Company. And write a loop that loops throug the columns of that header row to collect all fields.

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