Macro question

My spreadsheet is as follows:
B C D E
2 6 5 6 7

B2 has the formula ='sheet1'!D7 which is linked to a web
query which is updated once per day. When the value
in 'sheet1'!D7 changes, B2 takes on the new value and C2
takes on the value of B2 and D2 takes on the value of C2
etc.

I am using the following macro,
Range("E2").value=Range("D2").value
Range("D2").value=Range("C2").value
Range("C2").value=Range("B2").value
Range("B2").value=""

The problem is that I am losing the cell reference in B2
ie: ='sheet1'!D7. Do you know how I should program the
macro to keep the reference to sheet1? Thanks.

Hi Jim
does this macro also update the web query?
If I understood you correctly this souds more like using a
worksheet_claculate event, checking if B2 has changed and if yes change
C2:E2 accordingly

--
Regards
Frank Kabel
Frankfurt, Germany


Jim wrote:
My spreadsheet is as follows:
B C D E
2 6 5 6 7

B2 has the formula ='sheet1'!D7 which is linked to a web
query which is updated once per day. When the value
in 'sheet1'!D7 changes, B2 takes on the new value and C2
takes on the value of B2 and D2 takes on the value of C2
etc.

I am using the following macro,
Range("E2").value=Range("D2").value
Range("D2").value=Range("C2").value
Range("C2").value=Range("B2").value
Range("B2").value=""

The problem is that I am losing the cell reference in B2
ie: ='sheet1'!D7. Do you know how I should program the
macro to keep the reference to sheet1? Thanks.

Jim,

The last line of the macro is clobbering your link, replacing it with " ".
Try leaving it out. Or as we say "remming it out" (putting an apostrophe in
front of it, turning it into a comment). I don't know when this macro runs,
so I don't know what you'll be left with.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Jim" wrote in message
...
My spreadsheet is as follows:
B C D E
2 6 5 6 7

B2 has the formula ='sheet1'!D7 which is linked to a web
query which is updated once per day. When the value
in 'sheet1'!D7 changes, B2 takes on the new value and C2
takes on the value of B2 and D2 takes on the value of C2
etc.

I am using the following macro,
Range("E2").value=Range("D2").value
Range("D2").value=Range("C2").value
Range("C2").value=Range("B2").value
Range("B2").value=""

The problem is that I am losing the cell reference in B2
ie: ='sheet1'!D7. Do you know how I should program the
macro to keep the reference to sheet1? Thanks.