Sub UpdatePrice()
Dim i As Long, v As Variant, s As String, res As Variant
Dim rng1 As Range, cell1 As Range, rng As Range
Dim rng2 As Range, sh As Worksheet

With Worksheets("Sheet4")
Set rng1 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With

v = Array("Sheet1", "Sheet2", "Sheet3")
For i = LBound(v, 1) To UBound(v, 2)
s = v(i)
Set sh = Worksheets(s)
With sh
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With

For Each cell1 In rng1
res = Application.Match(cell1, rng, 0)
If Not IsError(res) Then
Set rng2 = rng(res)
rng2.Offset(0, 3).Value = cell1.Offset(0, 4).Value
End If
Next cell1

Next i
End Sub
obviously test this on a copy of your workbook.

--
Regards,
Tom Ogilvy


"MT_Netols" wrote:


I have a somewhat lengthy question that I'm hoping someone can help me
with. I can't get a macro I'm trying to write to operate correctly, so
maybe someone knows a code that will help.

Here's the situation:
- I have four sheets (Sheet 1, 2, 3, & 4 for simplicity's sake), Sheets
1-3 contain stocks I'm looking at at various points of my analysis
stage. Sheet 4 contains a list of all the stocks from Sheets 1-3.
- I import the stock quotes on Sheet 4 so I can obtain the most current
prices for my stocks.
- Now, what I want to do is have my macro run through the list of
stock tickers on Sheet 4, and if it finds the corresponding ticker in
Sheet 1, I want it to copy in the new current price.
- For example: Stock XYZ is on Sheet 1, with the ticker in column A
and the current price in column D. The ticker is also on Sheet 4 with
the new current price in column E, so what I want to do is something
along the lines of:
For each A:A in Sheet4! If A = Sheet1! A:A Then
copy Sheet4! E:E into Sheet1! D:D

I hope what I'm trying to accomplish makes sense, I basically just want
to update what I have typed in for the current prices without having to
manually type them all in, so if I can have the macro run through the
list in Sheet4, look for it in Sheet1, and if it finds it, copy the
price in. I know that if I can get just one run of this to work I can
change the Sheet references to have it run for Sheets 1, 2, and 3, so
any help would be greatly appreciated.


--
MT_Netols
------------------------------------------------------------------------
MT_Netols's Profile: http://www.excelforum.com/member.php...o&userid=37665
View this thread: http://www.excelforum.com/showthread...hreadid=572758