Any way to Paste without Relative reposition?

[WebColin]

I often want to copy and paste a block of cells, but still have their
references point to the same cells as the original copied block. By default,
Excel changes all the references by the position change of the copy block.

For example if I copy a column of cells A that refers column M on another
worksheet, and paste it 3 columns to the right in column D, all its
references will now refer column P on that other worksheet. What if I still
want them to refer to column M?

I know I can do this one cell at a time by selecting the formula text in the
cell and pasting that, but if I have a large complex table, this is
immensely time consuming. What I really want is something like a "Paste
Contents" as a Paste Special option.

Any suggestions, or is this just a missing feature in Excel?

Thanks,
Colin

 

[Bernie Deitrick]

Colin,

The typical method is to cut the original cells, then paste them where you
want them, or just select, grab, and drag them to the new location.

If you want to completely duplicate the cells, you could simply link the new
table to the cells with the formula.

Or, if you need to copy them to change them slightly, you can use two
macros: the first to convert all the cells to strings, which you then copy
and paste, and a second that converts the strings to formulas (used twice,
once on the orignal table and again on the new table).

If you want to use the macros, I will post them....

HTH,

Bernie

MS Excel MVP

 

[Traveller]

The easiest solution, especially if you do this with some frequency, is to
use the free add-in ASAP Utilities. Under the primary category of "Range" is
an option to copy a range to another location without changing the cell
references. It works very simply and accurately.

 

[Shane Devenshire]

Hi,

1. If the cell references are absolute there will be no problem: instead
of =M1 it reads =$M$1
2. If you are only dealing with a single cell or you can copy on the
Formula Bar or you can put you cursor in the cell below and press Ctrl+'
(control and apostrophy)

3. You can copy a block and choose Paste Special, Links.

4. And finally the tricky way:
- Suppose the formula are in A1:A10 select them and copy to cells A11:A20,
- Move the cells from A11:A20 to C11:C20 (cut and paste won't change the
references.
- Copy the cells from C11:C20 to C1:C10

The cells in C1:C10 will have the same references as those in A1:A10

If these help, please click the Yes button

Cheers,
Shane Devenshire

 

[Crownman]

Colin,

The typical method is to cut the original cells, then paste them where you
want them, or just select, grab, and drag them to the new location.

If you want to completely duplicate the cells, you could simply link the new
table to the cells with the formula.

Or, if you need to copy them to change them slightly, you can use two
macros: the first to convert all the cells to strings, which you then copy
and paste, and a second that converts the strings to formulas (used twice,
once on the orignal table and again on the new table).

If you want to use the macros, I will post them....

HTH,

Bernie

MS Excel MVP










- Show quoted text -

Colin:

I have occasion to use this process to copy ranges to a corresponding
range in a different workbook. I have been doing this by manually
converting the formulas to strings. If your macros will automate this
process I would appreciate it if you could post them.

Thank you

Crownman

 

[Bernie Deitrick]

Crownman,

I have occasion to use this process to copy ranges to a corresponding
range in a different workbook. I have been doing this by manually
converting the formulas to strings. If your macros will automate this
process I would appreciate it if you could post them.

Below are the two macros that I use - copy the code into your personal.xls, then add two custom
buttons to a toolbar, and assign the macros to those buttons.

HTH,
Bernie
MS Excel MVP

Sub SAFormulaToText()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In Selection.SpecialCells(xlCellTypeFormulas)
myCell.Formula = "'" & myCell.Formula
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub

Sub SATextToFormula()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub

 

[Crownman]

Crownman,


Below are the two macros that I use - copy the code into your personal.xls, then add two custom
buttons to a toolbar, and assign the macros to those buttons.

HTH,
Bernie
MS Excel MVP

Sub SAFormulaToText()
Dim myCell As Range
Dim myCalc As Variant

With Application
   .ScreenUpdating = False
   myCalc = .Calculation
   .Calculation = xlCalculationManual
   .EnableEvents = False
End With

On Error Resume Next

For Each myCell In Selection.SpecialCells(xlCellTypeFormulas)
   myCell.Formula = "'" & myCell.Formula
Next myCell

With Application
   .ScreenUpdating = True
   .Calculation = myCalc
   .EnableEvents = True
End With
End Sub

Sub SATextToFormula()
Dim myCell As Range
Dim myCalc As Variant

With Application
   .ScreenUpdating = False
   myCalc = .Calculation
   .Calculation = xlCalculationManual
   .EnableEvents = False
End With

On Error Resume Next

For Each myCell In Selection
   myCell.Formula = myCell.Text
Next myCell

With Application
   .ScreenUpdating = True
   .Calculation = myCalc
   .EnableEvents = True
End With
End Sub

Bernie:

Thank you for your help!

Crownman