Update data from mapping table to another worksheet

[Kartyk]

Hi,,

I need help urgently on a macro to fill a spreadsheet based on a mapping table.

Mapping sheet givves us the mapping table. Column header marked in yellow is the primary key.


Destination will have this header in any column of the worksheet. VBA should identify the
the column and then values under those and systematicallyy fill the rest of the columns
again spread anywere in the destination sheet.


For eg. Outright should have NDF, FX & Cash on the same row under respective headers.


Attaching file for better understanding.


Thanks
Test.xlsm

[Paul_Hossler]

1. Please rely on using the forum, and not Prive Messages

2. If I understand, something like this

Option Explicit
Sub test()
    Dim wsDest As Worksheet, wsMap As Worksheet
    Dim i As Long, hcell As Range, HeadersOne As Range
    Dim colInstrumentType As Long, colProductType As Long, colProductSubType As Long, colProductCode As Long
    Set wsDest = Sheets("Destination")
    Set wsMap = Sheets("Mapping")
    
    Application.ScreenUpdating = False
    
    With Application.WorksheetFunction
        colInstrumentType = .Match("Instrument Type", wsDest.Rows(1), 0)
        colProductType = .Match("Product Type", wsDest.Rows(1), 0)
        colProductSubType = .Match("Product sub Type", wsDest.Rows(1), 0)
        colProductCode = .Match("Product Code", wsDest.Rows(1), 0)
    End With
    
    
    With wsDest
        For i = 2 To wsMap.Cells(1, 1).CurrentRegion.Rows.Count
            .Cells(i, colProductCode).Value = wsMap.Cells(i, 1).Value
            .Cells(i, colInstrumentType).Value = wsMap.Cells(i, 2).Value
            .Cells(i, colProductType).Value = wsMap.Cells(i, 3).Value
            .Cells(i, colProductSubType).Value = wsMap.Cells(i, 4).Value
        Next i
    End With
    
    Application.ScreenUpdating = True
End Sub

3. I always like to use meaningful names for variables, like wsDest instead of Sh1 since it helps me to follow my own code after 6 months.

4. If you need something else, just ask

[Kartyk]

Thanks for the response.

However, not sure if the macro addresses the issue fully. Columns can be anywhere in the destination worksheet and there can be any number of rows for the primary key.

So, macro has to first identify the primary key column and read its value. Based on that it has to fill in other columns as well.

[Paul_Hossler]

OK, your test data only used a primary key one time in Mapping sheet so it was easy to map to Destimation sheet

Maybe you could post a more realistic example with the amounts and types of data that are expected