saving data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

RE: Excel 2002 (version 10)

Hope someone can help.

I am setting up a project for my pupils at school and I need to get it
working before I ask them to do it.

I have part A working but how do I do part B, is it easy to do they need
to write code?

Part A (This part work - this is for information only)
They will all have an imaginary 1000 shares of an American company of
their choice.

They are to set up a spread sheet to automatically go to a web site and
get the stock price and the exchange rate and do some calculations.

Well this all works great. An auto updates when the spreadsheet is
opened.
It is saved when the spreadsheet is closed.


Part B
This should be automated also.

All a user should do is open the spreadsheet and the information is
there and archived. At the moment it only keeps the current data.

I want them to take the DATE, SHARE PRICE, EXCHANGE RATE
and copy these values to a second sheet in the spreadsheet (called
archive) and add it to the bottom of the appropriate column.

So they need a way of finding the next available cell in the appropriate
column on the Archive sheet and inserting the data.

Is this a simple thing for them to do (they are year 10 students) or
does it require a program in VBA or something.

I don't want to ask them to do something that is too complicated.


Part C is to make graphs with the data as the year goes on but I know
how to do this, as do most of my students.

Any help will be most welcome, even if its to say its too complicated
for them.

Thanks
 
Part B is easy to do manually: copy the data, go to the archive sheet,
select the top cell of the appropriate column, hit end, down-arrow,
down-arrow and Ctrl-V. You can also have them write a quick macro, but the
complexity will depend on how your column headers are set up, and how your
data is arranged.

The VBA to do the same can be recorded making judicious use of Relative
References, and you'll get something like

Range("A1:C1").Select
Selection.Copy
Sheets("Archive").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste


which is close, and only needs a little editing by you:

Range("A1:C1").Select 'Use the actual range address here
Selection.Copy
Sheets("Archive").Select
Range("A1").Select 'Change to the top cell of the archive column
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste

But Excel rarely needs to select cells, so this one line will work (it will
look like two since I use the continuation character to insure it can be
copied)

Range("A1:C1").Copy _
Sheets("Archive").Range("A1").End(xlDown)(2)

Which is often better written

Range("A1:C1").Copy _
Sheets("Archive").Range("A66536").End(xlUp)(2)

Of course, change your range addresses to reflect reality.

HTH,
Bernie
MS Excel MVP
 
Thanks for the info.

I think this is a bit much for my students so I will tell them to skip
part B.

Thanks for the info. I will get it to work and give them the info.

Thanks again.
 
Back
Top