Thread: Updating dynamic data into excel database

I am currently downloading stock information from companies I follow, and recording the closing data each day.

I would like to automate the process of recording each days data.

Here is my setup:
Sheet1 is where I import the stock price each day. This data is retrieved from internet, and then cleared out the following day.

Sheet2 is the "database", where I store the previous closing prices.

Currently, the Stock Price column in Sheet2 is an if function that pulls data based on a date match to Sheet1. The problem, is that as Sheet1 is refreshed (along with the date), my if formula will clear out the previous days data in Sheet2.

I want to create a macro that copies the current stock price (if formula), then pastes values back into the same cell. But.. it needs to be dynamic, so that the next time data is refreshed, it is copy/pasting on the next row down.

How can I do this?
or..
Is there a better way to do what I am trying to accomplish?


Thanks!

Ok I think I am on the right path with the following macro, but still need help completing it.


Sub Stock()
Set i = Sheets("Sheet1").Range("A1")
Set e = Sheets("Database").Range(B)
Set f = Sheets("Database").EntireRow


If i.Range("E1") = e.Range("B") Then
Set NewRange =


Range("NewRange").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("NewRange").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub


How do I identify a NewRange, which should be the entire row in 'Database' where the date value in Column B matches A1 in 'Sheet1'?