VBA Extract data from specific column on another sheet

0
votes

I have 2 sheets which I extract from the system. For sheet1(Data) contain multiple column and all the data inside. For sheet2(Get) I have 2 column, as per below. With reference column no 2 (ID) on sheet2(Get), I want to search this value in the sheet1(Data) then extract specific column value. I try to search online for example code and found this piece of code which extract all column value. But I only want to extract column with highlighted with yellow then extract this value into sheet2(Get). Can help me to modified this code?

Note: For sheet2(Get), all the data on both column A and B already prefilled so I would like to change Worksheet_SelectionChange into a normal sub then run this sub using macro. Possible?

Colomn C (Get sheet) should extract from Colomn B (Data sheet)
Colomn D (Get sheet) should extract from Colomn M (Data sheet)
Colomn E (Get sheet) should extract from Colomn J (Data sheet)
Colomn F (Get sheet) should extract from Colomn L (Data sheet)
Colomn G (Get sheet) should extract from Colomn C (Data sheet)
Colomn H (Get sheet) should extract from Colomn G (Data sheet)

sheet2(Get) enter image description here

sheet1(Data) enter image description here

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim k As Integer, i As Long, Str As Range
    'row number
    For i = 3 To Sheets("GetData").Range("a65536").End(3).Row
        Set Str = Sheets("Data").Range("a:a").Find(Cells(i, 1).Value, , xlValues, xlWhole)
        If Not Str Is Nothing Then
            'column number
            For k = 1 To 14
                If k > 1 Then Sheets("GetData").Cells(i, k).Value = Sheets("Data").Cells(Str.Row, k).Value
            Next k
        Else
            For k = 2 To 14

                Sheets("GetData").Cells(i, k).Value = "Null"
            Next k

        End If
    Next i
End Sub
2
excelvba
You can use VLOOKUP for this - no VBA required. - Tim Williams
Thanks, but I prefer to use VBA instead to avoid formula inside my data. I know I can paste value on the helper sheet but I would like to avoid that. - amein
Have you tried anything to modify this code? What problems did you run into? - Tim Williams
Im stucked at to choose specific column instead of all column, can you give hint how to do it? - amein

2 Answers

0
votes

Once you have the row you can copy like this:

Dim col As Long
'...
'...
col = 3
For Each e In Array(2, 3, 7, 10) 'columns to fetch
    Sheets("GetData").Cells(i, col).Value = Str.EntireRow.Cells(e).Value
    col = col + 1
Next 
'...
'...
0
votes
Public Sub fill_data()
    Dim k As Integer, i As Long, Str As Range, col As Long, e As Variant
    'row number
    For i = 2 To Sheets("GetData").Range("B65536").End(3).Row
        Set Str = Sheets("Data").Range("A:A").Find(Cells(i, 2).Value, , xlValues, xlWhole)
        If Not Str Is Nothing Then 'if not empty
        col = 3
            For Each e In Array(13, 2, 10, 3, 5, 12, 9) 'columns to fetch
            Sheets("GetData").Cells(i, col).Value = Str.EntireRow.Cells(e).Value
            col = col + 1
            Next
        Else 'if empty
        For col = 3 To 9
            'Sheets("GetData").Cells(i, col).Interior.ColorIndex = 16 'change cell color OR
            Sheets("GetData").Cells(i, col).Value = "NOTFOUND" 'change cell text
            Next col
        End If
    Next i
End Sub

Thank you to Tim Williams for you help.