Untitled

Sub PullProductDescriptionsAndDynamicColumn()

    Dim wsCurrent As Worksheet
    Dim wsPrices As Worksheet
    Dim lastRowCurrent As Long
    Dim lastRowPrices As Long
    Dim currentCode As String
    Dim dynamicHeader As String
    Dim dynamicHeaderDecimal As Double
    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 rows in both sheets
    lastRowCurrent = wsCurrent.Cells(wsCurrent.Rows.Count, "A").End(xlUp).Row
    lastRowPrices = wsPrices.Cells(wsPrices.Rows.Count, "A").End(xlUp).Row
    
    ' Get the value in F1 of the Paste sheet (dynamic header)
    dynamicHeader = wsCurrent.Range("F1").Value
    
    ' Check if F1 contains a percentage and convert it to decimal
    If InStr(dynamicHeader, "%") > 0 Then
        dynamicHeaderDecimal = CDbl(Replace(dynamicHeader, "%", "")) / 100
    Else
        dynamicHeaderDecimal = dynamicHeader
    End If
    
    ' 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 (either as text or decimal)
    Set headerCell = headerRow.Find(What:=dynamicHeaderDecimal, 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