Code to get Excel to record the value in a cell(updated daily) into a database

[JBGH]

I am new to excel (excel 2011 for Mac OSX)

I set up MSN stock tracker so that on "Sheet 2" I have the MSN app and on "sheet 1" the company names in Column 1 and the price in column 2 - The cells in sheet one update every time I refresh MSN data.

Looks like this....

..........A.......B
1.......XOM....80
2.......CVX....100
etc
etc

My goal now is to set up Sheet 3 that will record the closing price everyday so that I can have a database of the closing prices to create charts, etc.

Something similar to this...

........A..............B........C........D.......E
1....DATE.........XOM.....CVX....WMT...BAC
2....1.24.11.......80.......100
3....1.25.11.......79.......101
etc
etc

*All the periods are just space holders to make it look like excel sheet

I have been referencing this thread

http://excel.bigresource.com/Track/excel-XHXPae0d/

but cannot seem to get it to work. I cannot figure out where to enter that formula, etc.

I tried messing around with Macros & the formula =CELL , to grab the data, but am having no luck.


Any help would be greatly appreciated!

Thanks

 

[venkat1926]

in sheet1 date should be somewhere.
see sample sheet1 bellow. the date is in A1 in sheet1
this data is refreshed at the end of every day I presume.
in this sheet select B3 to end of that column and click edit copy
go to sheet 2 select B1 and edit paste
this you have to do only once unless you add some more stocks

then run this macro at the end of the day

Sub test()
Worksheets("sheet1").Activate
Range("a1").Copy Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Range(Range("B3"), Range("B3").End(xlDown)).Copy
Worksheets("sheet2").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End Sub

Excel Workbook
	A	B
1	1/24/2011
2	NAME	PRICE
3	XOM	80
4	CVX	100
Sheet1

 

[JBGH]

Thanks for the response, but I cannot get the code to work.

I am very new to excel. Do I just open the "Macro" window and paste this code into it? Or click record marco, then paste the data, then paste this code?

Also, If the numbers I want transfered in are in the range "B1:B9", shouldn't that replace 'a1' in your code?


Worksheets("sheet1").Activate
Range("a1").Copy Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Range(Range("B3"), Range("B3").End(xlDown)).Copy
Worksheets("sheet2").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False

 

[venkat1926]

don't worry. Everybody was new to excel at some time

if a macro is given by a third party how to run:

open vb editor (alt+F11)
hit control+R
project window opens on left
all open workbooks (including hidden workbooks) are listed.
highlight your relevant file
click insert in menu bar and click "module"
the module window opens
in that window copy paste the macro
save the file

now go to the sheet
click tools-macros-macro
macro window opens
in the bottom click the small window and choose "this workbook"
the macros in your present workbook will be listed
highlight the macro you want to run and click RUN on the right


now you should not change A1 if the date is in A1

but if your relevant range is B1 to B9 ( with no gap)
change B3 to B1(only this statement. others remain same


Range(Range("B1"), Range("B1").End(xlDown)).Copy</pre>
if there is any further doubt do not hesitate to post back