vba code to extract data from data table and update data sheet

Post #3 code and workbook updated to Final Versions 25 May 2017 12:50
I didn't get an answer to my question, so I set Column AT = corresponding 'Table 1' values (Column D) in rows where no SADC was found. Hope this suits-Lee

Sub DataSheetUpdate()

Dim SourceRange As Range
Dim DestinationRange As Range
Dim found As Range
Dim LookupRange As Range
Dim sLastRow As Long
Dim dLastRow As Long
Dim rw As Long
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim SADCs As Boolean

Set WS1 = Worksheets("table 1")
Set WS2 = Worksheets("DATA SHEET")

With WS1
    Set SourceRange = .Columns("C:F")
    sLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set LookupRange = .Cells(2, "A").Resize(sLastRow - 1)
End With

With WS2
    Set DestinationRange = .Columns("AS:AV")
    dLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

    Set found = .Columns("K").Find("SADC*(", LookIn:=xlFormulas, Lookat:=xlPart)
    If Not found Is Nothing Then
        .Cells(1, "AW").Resize(dLastRow).Copy
        .Cells(1, "AX").PasteSpecial xlPasteFormats
        .Cells(1, "AX") = "SADC"
        SADCs = True
        Application.CutCopyMode = False
    End If

For rw = 2 To dLastRow
    Set found = LookupRange.Find(.Cells(rw, "I"))
    If found Is Nothing Then
        DestinationRange.Rows(rw) = "XXX"
        Debug.Print "Tariff # not found on row " & rw
    Else
        DestinationRange.Rows(rw).Value = SourceRange.Rows(found.Row).Value
        .Cells(rw, "AW").Value = WS1.Cells(found.Row, "O")
        If SADCs Then
            On Error Resume Next
            If Left(.Cells(rw, "K"), 4) = "SADC" Then
                .Cells(rw, "AT") = 0
                .Cells(rw, "AX") = "Y"
            Else
                .Cells(rw, "AX") = "N"
            End If
            On Error GoTo 0
        End If
    End If
Next

End With

End Sub

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee