VBA Relative Value Copy, New User Need Help

[mattp24]

Hello I am a new user to VBA and need some help learning how to create a macro that searches for a value and then copies and pastes the data associated with that value on a new worksheet.

On worksheet 1 I have the bloomberg list of ticker data, which is consistently updating.

here is what the data looks like on worksheet1, each line is a new row:


C D E F G H
Ticker name
data data data data data
data data data data data
data data data data data

Ticker name
data data data data data

Ticker name
data data data data data
data data data data data



On worksheet 2 I want to be able to type in a ticker name and then click the macro. The ticker names are in all caps and three letter long (i.e. MMM)

1. So I want to type in a ticker name in worksheet2 cell B2
2. Then click my macro
3. The program search for the ticker name on worksheet1 column C
4. The program copies the data associated with the ticker name
5. The program pastes this data onto worksheet2 cell B4.

I understand this may be very complex, but if anyone has any ideas I would be extremely grateful. Thank you.

 

[Jonmo1]

Try this macro

Sub test()
Set WS1 = Sheets("Sheet1")
Set WS2 = Sheets("Sheet2")
x = WS2.Range("B2").Value

'clear current contents
LR = WS2.Cells(4, "B").End(xlDown).Row
If LR = 65536 Then LR = 3
WS2.Range("B4:H" & LR).ClearContents

On Error Resume Next
BR = WorksheetFunction.Match(x, WS1.Columns(3), 0) + 1
If BR = "" Then
    MsgBox "Not Found"
    Exit Sub
End If

ER = WS1.Cells(BR, "C").End(xlDown).Row

WS1.Range(WS1.Cells(BR, "C"), WS1.Cells(ER, "H")).Copy WS2.Range("B4")

End Sub

 

[mattp24]

Yeah that works awesome thanks. One last questions, do you know how I would write an if statement in the same macro saying that if the data retrieved was greater than 11 lines put Yes in B28, otherwise put No?

Here is what I have i dont think it is right though.

If Count(Worksheets("sheet2").Cells("B5:B17")) > 11 Then
Worksheets("sheet2").Cells("B28").Value = "Yes"
Else
Worksheets("sheet2").Cells("B28").Value = "NO"

 

[Jonmo1]

Try

If Worksheets("sheet2").Range("B5:B17").Rows.Count > 11 Then


But to be more specific to your macro, it would be

If WS1.Range(WS1.Cells(BR, "C"), WS1.Cells(ER, "H")).Rows.Count > 11 Then