Untitled

Sub PullProductDescriptionsAndDynamicColumn()

    Dim wsCurrent As Worksheet
    Dim wsPrices As Worksheet
    Dim lastRowCurrent As Long
    Dim currentCode As String
    Dim dynamicHeader As String
    Dim dynamicCol As Long
    Dim i As Long
    Dim matchRow As Range
    Dim headerRow As Range
    Dim headerCell As Range
    
    ' Set worksheets
    Set wsCurrent = ThisWorkbook.Sheets("Paste")
    Set wsPrices = ThisWorkbook.Sheets("Prices")
    
    ' Find the last row in the Paste sheet
    lastRowCurrent = wsCurrent.Cells(wsCurrent.Rows.Count, "A").End(xlUp).Row
    
    ' Get the value in F1 of the Paste sheet (dynamic header)
    dynamicHeader = wsCurrent.Range("F1").Value
    
    ' Find the header row in Prices sheet (assume it's row 2)
    Set headerRow = wsPrices.Rows(2)
    
    ' Locate the column number for the header matching the value in F1
    Set headerCell = headerRow.Find(What:=dynamicHeader, LookIn:=xlValues, LookAt:=xlWhole)
    If Not headerCell Is Nothing Then
        dynamicCol = headerCell.Column
    Else
        MsgBox "Header '" & dynamicHeader & "' not found in Prices sheet!", vbExclamation
        Exit Sub
    End If
    
    ' Loop through each code in the Paste sheet
    For i = 2 To lastRowCurrent
        currentCode = wsCurrent.Cells(i, "A").Value
        
        ' Check if the code exists in the Prices sheet
        Set matchRow = wsPrices.Columns("A").Find(What:=currentCode, LookIn:=xlValues, LookAt:=xlWhole)
        
        ' If a match is found, pull the description from column B and the value from the dynamic column
        If Not matchRow Is Nothing Then
            wsCurrent.Cells(i, "B").Value = wsPrices.Cells(matchRow.Row, "B").Value ' Pull description
            wsCurrent.Cells(i, "C").Value = wsPrices.Cells(matchRow.Row, dynamicCol).Value ' Pull dynamic column value into C
        Else
            wsCurrent.Cells(i, "B").Value = "Not Found"
            wsCurrent.Cells(i, "C").Value = "Not Found"
        End If
    Next i

    MsgBox "Descriptions and dynamic column values updated!", vbInformation

End Sub