Incremental Cell Movement

[Guest]

I am using this to move the value in cell E33 to Sheet3 cell E28 but what I'd
like to be able to do is move cell E33 to sheet3 E28 the first run through
and then next run
E33 should go to sheet3 E29 and so on. Is this possible.


Worksheets("sheet1").Range("e33").Copy
Worksheets("sheet3").Range("e28").PasteSpecial _
xlPasteValues
Application.CutCopyMode = False

 

[Guest]

I suppose you want it to be written to the first blank cell in column E, am i
right?
In this case:
Worksheets("sheet1").Range("e33").Copy
Worksheets("sheet3").Range("E65536").End(xlup).offset(1,0).PasteSpecial _
xlPasteValues
Application.CutCopyMode = False

Regards,
Sébastien

 

[Tom Ogilvy]

Dim rng as range
Worksheets("sheet1").Range("e33").Copy
set rng = Worksheets("sheet3").Range("e28")
if not isempty(rng) then
if isempty(rng.offset(1,0)) then
set rng = rng.offset(1,0)
else
set rng = rng.End(xldown)
end if
End if
rng.PasteSpecial _
xlPasteValues
Application.CutCopyMode = False