How to move cell contents

S

Stephane

Hi,

I need to move the content of a cell by x number of columns (i.e. A1 to A4)
but I cannot have a formula in the destination cell. How can I do that easily?

thanks,
 
J

JulieD

Hi Stephane

not sure what you mean here, do you want to
1) take the value (which is derived from a formula in A1) and put it in A4
i.e. you have in A1 =B1-B2 and this gives an answer of 5, now you want the 5
(but not the formula in A4)
if so, then copy (or cut) A1, click in A4 and choose edit / paste special -
values

2) do you want to put the same value in A4, which is in A1 so that if A1
changes the value in A4 also changes
e.g. in A1 you have 7, you want the 7 also to appear in A4. If you change
the A1 7 to 5, then A4 should change to 5.
if so, and you don't want a formula in A4 you will need to use a
worksheet_change event (ie VBA code) - please let us know if this is what
you're after and we'll come up with the code for you.

something else entirely, if so, could you please provide a few more details.

Cheers
JulieD
 
E

Earl Kiosterud

Stephanie,

You didn't tell all. Did you mean move, or copy? If the cell in A1 contains
a value (non-formula), you can move it to A4 by cutting/pasting, or more
easily be dragging it by it's border. If the cell contains a formula, and
you want the result of the formula in A4, you can do that by doing Copy,
then in A4 do Paste special - Values. Now delete A1. If neither is the
solution you need, perhaps you can describe what's going in in more detail.
 
J

JulieD

Hi Stephane

okay, right mouse click on the sheet tab of the sheet where you want this to
happen and choose
view code
in the white area on the right of the screen paste the following code in
----
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
Range("A4").Value = Range("A1").Value
End If

End Sub
 
S

Stephane

Hi Julie,

this seems to be exactly what I need but I have another twist for you:
imagine that I have to replicate this for a large number of cells.

thank you for you invaluable help!
 
D

Dave Peterson

What is the large number of cells and where do you copy the value?

I'm guessing the cells are in column A and you always copy 3 columns to the
right (column D).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Target, Me.Range("a:a"))
On Error GoTo 0

If myRng Is Nothing Then Exit Sub

On Error Resume Next 'keep going through all the cells
Application.EnableEvents = False
For Each myCell In myRng.Cells
myCell.Offset(0, 3).Value = myCell.Value
'myCell.ClearContents 'wipe out the old value?
Next myCell
Application.EnableEvents = True
On Error GoTo 0

End Sub
 
Top