creating a list with dates from a single cell

[mill_rat]

Help! I want to create a list with dates based on a value in a single
cell. eg sheet1 has cell A1 which is referenced to other worksheets
some of which are updated by web queries. Cell A2 is today(). How can I
create a list on sheet2 which stores the value of A1 and A2 (sheet1) in
a new row every time sheet2 is opened?
Would I need to run a macro to do this? If so How?

 

[Steve Smallman]

Mill_rat,

basically the answer to you rquestion is yes, you will need to code the
Workbook_open event.

Go to Tools/Macro/Visual Basic Editor. From the list of Microsoft Excel
Objects in Project Explorer, select ThisWorkbook. This should take you to
the workbook open event, if not open the code window, in the left hand drop
down, select workbook rather than General and in the right hand select open.

between
Private Sub workbook_open
and
End Sub

enter the following code:

Sheets("Sheet2").Select 'selects sheet 2
Range("A1").Select 'makes sure cursor in right spot
Selection.End(xlDown).Select 'goes to thebottom of the current list
ActiveCell.Offset(1, 0).Select 'goes down one more row
ActiveCell.Value = Sheets("sheet1").Range("a1").Value 'sets the selected
cell to the value in A1 on Sheet1
ActiveCell.Offset(0, 1).Value = Sheets("Sheet1").Range("a2").Value 'sets
next cell right to A2
Sheets("sheet1").Select 'optional, flicks across to Sheet 1
Range("A1").Select 'optional goes to Home


Point to note:
make sure that Sheet 2 has two rowsof info beofre permitting this to run.
Otherwise you will strik problems with the positioning of the values. In my
test work book, I put two rows of headings


Steve