Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Transfering informationfrom one cell to another
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
OK.
The code I gave you will do that. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |