transfering information from one cell to another

G

garr

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
(e-mail address removed)
 
N

nbrcrunch

You'd need a VBA (Visual Basic for Applications) module for that. There
is no built-in function.
 
O

Otto Moehrbach

Garr
Provide some detail about the layout of your data in each sheet. HTH
Otto
 
G

garr

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
 
O

Otto Moehrbach

I think I understand what you want except for the phrase "in a different
column". Explain that please.
I take it that you download stock data into one or more rows of sheet
one. And you want all the data in sheet one transferred to sheet 2 but one
column to the right leaving Column A of each row for today's date. Is that
correct? HTH Otto
 
O

Otto Moehrbach

This macro does what you want, if I read you correctly.
Post back if this isn't what you want. Expand this message to full screen
to avoid line wrap. HTH Otto
Sub MoveData()
Dim RngA1 As Range
Dim Dest As Range
Dim i As Range
Set RngA1 = Range("A1", Range("A" & Rows.Count).End(xlUp))
With Sheets("Two")
If .[A1] = "" Then
Set Dest = .[A1]
Else
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End With
For Each i In RngA1
Range(i, Cells(i.Row, "IV").End(xlToLeft)).Copy Dest.Offset(, 1)
Dest = Date
Set Dest = Dest.Offset(1)
Next i
End Sub
 
G

garr

Hi Otto I've tried to run your macro and I get
Run Time Error 9
Sub Script Out of Range and in VB the line reads

With Sheets ("two")

is high lited what next

Thanks Garry


Otto Moehrbach said:
This macro does what you want, if I read you correctly.
Post back if this isn't what you want. Expand this message to full screen
to avoid line wrap. HTH Otto
Sub MoveData()
Dim RngA1 As Range
Dim Dest As Range
Dim i As Range
Set RngA1 = Range("A1", Range("A" & Rows.Count).End(xlUp))
With Sheets("Two")
If .[A1] = "" Then
Set Dest = .[A1]
Else
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End With
For Each i In RngA1
Range(i, Cells(i.Row, "IV").End(xlToLeft)).Copy Dest.Offset(, 1)
Dest = Date
Set Dest = Dest.Offset(1)
Next i
End Sub
garr said:
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
 
G

Gord Dibben

Do you have a sheet named "two"(no quotes).

If not, change Otto's "two" to the name of your sheet.

e.g. "Sheet2"


Gord Dibben Excel MVP

Hi Otto I've tried to run your macro and I get
Run Time Error 9
Sub Script Out of Range and in VB the line reads

With Sheets ("two")

is high lited what next

Thanks Garry


Otto Moehrbach said:
This macro does what you want, if I read you correctly.
Post back if this isn't what you want. Expand this message to full screen
to avoid line wrap. HTH Otto
Sub MoveData()
Dim RngA1 As Range
Dim Dest As Range
Dim i As Range
Set RngA1 = Range("A1", Range("A" & Rows.Count).End(xlUp))
With Sheets("Two")
If .[A1] = "" Then
Set Dest = .[A1]
Else
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End With
For Each i In RngA1
Range(i, Cells(i.Row, "IV").End(xlToLeft)).Copy Dest.Offset(, 1)
Dest = Date
Set Dest = Dest.Offset(1)
Next i
End Sub
garr said:
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



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
(e-mail address removed)
 
G

garr

Well changing the two, to the name of the second sheet did work, but this
macro enters the date starting in A1 then in A2 and B2 and so on in sheet 2.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top