I'm using Excel 2003
I have a workbook open and am using 2 worksheets. On sheet 1 I get
information from the internet and then transfer three cells of it to sheet
2. Sheet 1 updates every time I open the workbook. How do I get sheet 1 to
put the information into a different cell in sheet 2 each time it updates. I
would like to get them to follow the dates down a column.

Garry

Garr wrote:
How do I get sheet 1 to
put the information into a different cell in sheet 2 each time it updates. I
would like to get them to follow the dates down a column.

You will need a macro to do this.
It might go something like this, stored in a standard VBA module:

Sub TransferData()
Dim vRow
With ThisWorkbook.Sheets("Sheet2").Range("A1").CurrentR egion
' the used area starting at A1
vRow = Application.Match(CLng(Date),.Columns(1),0) ' match date in col. A
If IsError(vRow) Then
vRow = .Rows.Count+1 ' not found, add at end
.Cells(vRow,1)=Date ' add date to bottom of table
End If
' copy the newly received data
ThisWorkbook.Sheets("Sheet1").Range("A2:C2").Copy
' paste into sheet 2 as values
.Cells(vRow, 2).PasteSpecial xlValues
End With
End Sub

You could attach this macro to a button which you press, or you could have it
executed automatically, say 5 seconds after opening the workbook by adding

Sub Auto_Open()
Application.OnTime Now+TimeValue("0:0:5"), "TransferData"
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

This is a little more info on what I'm tring to do
I have down loaded stock info into sheet 1 and would like to transfer it to
sheet 2. Each piece of info in the cells in sheet one will be transferred to
cells in sheet two into a different column with a date of that day at the
beginning of each row on sheet two. Every day I will update sheet one and
would like it to update the info on sheet two in the next row down and not
update the previous row (leave the previous row with the last days info)

Garr

"garr" wrote in message
news:SzfRd.417386$8l.358150@pd7tw1no...
I'm using Excel 2003
I have a workbook open and am using 2 worksheets. On sheet 1 I get
information from the internet and then transfer three cells of it to sheet
2. Sheet 1 updates every time I open the workbook. How do I get sheet 1 to
put the information into a different cell in sheet 2 each time it updates.
I
would like to get them to follow the dates down a column.

Garry

OK.
The code I gave you will do that.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Bill I get an error
run time error 9
subscript out of range
at line 3
With ThisWorkbook.Sheets("Sheet2").Range("A1").CurrentR egion



"Bill Manville" wrote in message
...
Garr wrote:
How do I get sheet 1 to
put the information into a different cell in sheet 2 each time it
updates. I
would like to get them to follow the dates down a column.

You will need a macro to do this.
It might go something like this, stored in a standard VBA module:

Sub TransferData()
Dim vRow
With ThisWorkbook.Sheets("Sheet2").Range("A1").CurrentR egion
' the used area starting at A1
vRow = Application.Match(CLng(Date),.Columns(1),0) ' match date in col.
A
If IsError(vRow) Then
vRow = .Rows.Count+1 ' not found, add at end
.Cells(vRow,1)=Date ' add date to bottom of table
End If
' copy the newly received data
ThisWorkbook.Sheets("Sheet1").Range("A2:C2").Copy
' paste into sheet 2 as values
.Cells(vRow, 2).PasteSpecial xlValues
End With
End Sub

You could attach this macro to a button which you press, or you could have
it
executed automatically, say 5 seconds after opening the workbook by adding

Sub Auto_Open()
Application.OnTime Now+TimeValue("0:0:5"), "TransferData"
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

I assumed the sheet to which you would be adding the results was named
Sheet2. Replace Sheet2 with the actual name of your sheet.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup