I have a table in which in the cells C10:H10 there are different names of items and each column has 10 numerical values. I need to create a macro in which the user inserts the name of the item that he wants, and the macro is going to copy all the numerical values of that column and paste them in another table.
I was thinking of doing that with HLOOKHUP but I don’t know how to do that.
I tried with the following code but it doesn’t do what I want:
Sub copy() Dim answer As Variant answer = InputBox("Insert the name") Dim i As Integer, a As Range i = 0 For k = 0 To 5 Cells(10, 3 k).Select If ActiveCell = answer Then a = Activecell For x = 1 To 10 a.Offset(x, 0).Select Selection.Copy Range("K1").Select ActiveCell.Offset(i, 0).Select ActiveSheet.Paste i = i 1 Next End If Next End Sub