I have a workbook with cells on Sheet1 paste-linked to cells on
Sheet2.
Using a command button I need to be able to shift by one cell the
cells on Sheet1.
For example, if cell B3 is selected, I would click the command button
and cut cell B3 and insert it between cell B1 and B2, shifting the
other cells to the right, as shown below:
Private Sub CommandButton4_Click()
'move left
Selection.Cut
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.Insert Shift:=xlToRight
End Sub
But when I do this, the paste-link formulas on Sheet2 move as well.
I need the cells on Sheet 2 to remain linked to where they were
originally. Is this possible?
Thanks!

Cutting cells will automatically update the links. Instead copying will
prevent the links from changing.

Private Sub CommandButton4_Click()
'move left

ActiveCell.Offset(0, -1).Insert Shift:=xlToRight
ActiveCell.Offset(0, -1) = ActiveCell.Value
ActiveCell = ""

End Sub

" wrote:

I have a workbook with cells on Sheet1 paste-linked to cells on
Sheet2.
Using a command button I need to be able to shift by one cell the
cells on Sheet1.
For example, if cell B3 is selected, I would click the command button
and cut cell B3 and insert it between cell B1 and B2, shifting the
other cells to the right, as shown below:
Private Sub CommandButton4_Click()
'move left
Selection.Cut
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.Insert Shift:=xlToRight
End Sub
But when I do this, the paste-link formulas on Sheet2 move as well.
I need the cells on Sheet 2 to remain linked to where they were
originally. Is this possible?
Thanks!

On May 13, 9:04 am, Joel wrote:
Cutting cells will automatically update the links. Instead copying will
prevent the links from changing.

Private Sub CommandButton4_Click()
'move left

ActiveCell.Offset(0, -1).Insert Shift:=xlToRight
ActiveCell.Offset(0, -1) = ActiveCell.Value
ActiveCell = ""

End Sub



" wrote:
I have a workbook with cells on Sheet1 paste-linked to cells on
Sheet2.
Using a command button I need to be able to shift by one cell the
cells on Sheet1.
For example, if cell B3 is selected, I would click the command button
and cut cell B3 and insert it between cell B1 and B2, shifting the
other cells to the right, as shown below:
Private Sub CommandButton4_Click()
'move left
Selection.Cut
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.Insert Shift:=xlToRight
End Sub
But when I do this, the paste-link formulas on Sheet2 move as well.
I need the cells on Sheet 2 to remain linked to where they were
originally. Is this possible?
Thanks!- Hide quoted text -

- Show quoted text -

Thanks. I gave that a shot but it does not do it. I may not have been
clear in what I was trying to achieve.
Basically Sheet1 is a schedule, and jobs are listed in cells from left
to right in the order in which they will be done. Sheet 2 is formatted
differently and is for a printable version of sheet1.
Sometimes on sheet1 I need to rearange the jobs, and move up. For
instance, I need to move the data in Cell C3 to cell C2 and have the
data that was in cell C2 take the place of the data that was in Cell
C3. Basically just swapping C2 for C3. And my code did that but it
also swapped the paste link on Sheet2 so that now Sheet2 C3 is linked
to Sheet1 C2 and Sheet2 C2 is linked to Sheet1 C3. I need the paste
links on sheet2 to remain linked to the cells they were originally
linked to even if I move the cells around on Sheet1. Sorry, it's
simple but hard for me to explain.

Private Sub CommandButton4_Click()
'move left

temp = ActiveCell.Offset(0, -1)
ActiveCell.Offset(0, -1) = ActiveCell.Value
ActiveCell = temp

End Sub

" wrote:

On May 13, 9:04 am, Joel wrote:
Cutting cells will automatically update the links. Instead copying will
prevent the links from changing.

Private Sub CommandButton4_Click()
'move left

ActiveCell.Offset(0, -1).Insert Shift:=xlToRight
ActiveCell.Offset(0, -1) = ActiveCell.Value
ActiveCell = ""

End Sub



" wrote:
I have a workbook with cells on Sheet1 paste-linked to cells on
Sheet2.
Using a command button I need to be able to shift by one cell the
cells on Sheet1.
For example, if cell B3 is selected, I would click the command button
and cut cell B3 and insert it between cell B1 and B2, shifting the
other cells to the right, as shown below:
Private Sub CommandButton4_Click()
'move left
Selection.Cut
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.Insert Shift:=xlToRight
End Sub
But when I do this, the paste-link formulas on Sheet2 move as well.
I need the cells on Sheet 2 to remain linked to where they were
originally. Is this possible?
Thanks!- Hide quoted text -

- Show quoted text -

Thanks. I gave that a shot but it does not do it. I may not have been
clear in what I was trying to achieve.
Basically Sheet1 is a schedule, and jobs are listed in cells from left
to right in the order in which they will be done. Sheet 2 is formatted
differently and is for a printable version of sheet1.
Sometimes on sheet1 I need to rearange the jobs, and move up. For
instance, I need to move the data in Cell C3 to cell C2 and have the
data that was in cell C2 take the place of the data that was in Cell
C3. Basically just swapping C2 for C3. And my code did that but it
also swapped the paste link on Sheet2 so that now Sheet2 C3 is linked
to Sheet1 C2 and Sheet2 C2 is linked to Sheet1 C3. I need the paste
links on sheet2 to remain linked to the cells they were originally
linked to even if I move the cells around on Sheet1. Sorry, it's
simple but hard for me to explain.

On May 13, 10:04 am, Joel wrote:
Private Sub CommandButton4_Click()
'move left

temp = ActiveCell.Offset(0, -1)
ActiveCell.Offset(0, -1) = ActiveCell.Value
ActiveCell = temp

End Sub



" wrote:
On May 13, 9:04 am, Joel wrote:
Cutting cells will automatically update the links. Instead copying will
prevent the links from changing.

Private Sub CommandButton4_Click()
'move left

ActiveCell.Offset(0, -1).Insert Shift:=xlToRight
ActiveCell.Offset(0, -1) = ActiveCell.Value
ActiveCell = ""

End Sub

" wrote:
I have a workbook with cells on Sheet1 paste-linked to cells on
Sheet2.
Using a command button I need to be able to shift by one cell the
cells on Sheet1.
For example, if cell B3 is selected, I would click the command button
and cut cell B3 and insert it between cell B1 and B2, shifting the
other cells to the right, as shown below:
Private Sub CommandButton4_Click()
'move left
Selection.Cut
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.Insert Shift:=xlToRight
End Sub
But when I do this, the paste-link formulas on Sheet2 move as well.
I need the cells on Sheet 2 to remain linked to where they were
originally. Is this possible?
Thanks!- Hide quoted text -

- Show quoted text -

Thanks. I gave that a shot but it does not do it. I may not have been
clear in what I was trying to achieve.
Basically Sheet1 is a schedule, and jobs are listed in cells from left
to right in the order in which they will be done. Sheet 2 is formatted
differently and is for a printable version of sheet1.
Sometimes on sheet1 I need to rearange the jobs, and move up. For
instance, I need to move the data in Cell C3 to cell C2 and have the
data that was in cell C2 take the place of the data that was in Cell
C3. Basically just swapping C2 for C3. And my code did that but it
also swapped the paste link on Sheet2 so that now Sheet2 C3 is linked
to Sheet1 C2 and Sheet2 C2 is linked to Sheet1 C3. I need the paste
links on sheet2 to remain linked to the cells they were originally
linked to even if I move the cells around on Sheet1. Sorry, it's
simple but hard for me to explain.- Hide quoted text -

- Show quoted text -

That worked gret!!!!!!!! thanks VERY much!!!