Copying Excel Formulas

Use absolute references.

A relative reference means that the row or column reference is relative to the referencing cell and, when the formula is copied to other cells, the relative cell references are shifted such that the distance or offset between the referencing cell and the referenced cell remains the same. An absolute cell reference means that the row or column reference is fixed and does not change when the formula is copied to other cells.

With the A1 reference style a dollar sign is used to create an absolute reference. For example, the cell reference A$1, when copied to other cells, will always reference row 1 but the column may change if copied to another column. The cell reference $A$1 will allays reference cell A1 regardless of where it is copied.

Whether a row or column is an absolute or relative reference makes no difference in how the formula is calculated or the result it produces. They only have different effects when formulas are copied to other cells. For this reason, the decision as to whether cell references are absolute or relative should be made with regard to whether or not the formula will be copied to other cells. While entering each cell reference in a formula, consider how the referenced cell should be treated as the formula is copied to other rows and columns. If the referenced cell's row should be the same then make the row an absolute reference by placing a dollar sign in front of the row number. If the column should be the same then place a dollar sign in front of the column letter.

Note that relative references are not shifted when a formula is moved by either cutting and pasting or by dragging. Relative cell references are only adjusted when cells are copied.

Kevin

Manually, you would need to copy the formula directly from the formula bar rather than copying the cell.

Then you would paste it into the destination cell, again via the formula bar.

That's how I do it anyway perhaps there's an easier way.

In code you it's a little easier:

' copy the formula from A1 to Z1

Range("Z1").Formula = Range("A1").Formula

Well there you are, there are other ways to do it.

Knew I was missing something.:)

Deleted cross post

Go to the original cell
Click in the formula bar, then press CTRL+A then CTRL+C  (select all, copy)
Go to the destination cell
Click in the formula Bar
Press CTRL+V (paste)

This is the best solution to my question, and likely due to the fact I left one thing out: I had a block of several formulas I needed copied and pasted, without wanting to change the original cell references. I apologize for the omission of that fact.

The $absolute value suggestion would work, but would be too time-consuming, adding all the $absolute value designations to the formulas. The manual suggestions would work, too, but would also be time-consuming, for the same reason.

I had never heard or thought of replacing the "=" signs with "#", copying and pasting, and then reversing the replacements again. This worked great with just a few steps, and I'm back in business. Thanks, techfanatic, and everyone else who offered suggestions.

Although the answer is accepted but for the sake of completeness I would like to add this.

The accepted solution may work for this particular case but there would be cases where this may cause a problem. An example would be cases which contain #s within the formula and after completion of the process the original #s would be changed to =s

This can easily be taken care of by using a different character instead of the #

A more trouble free method which I use successfully and frequently is as follows


- Copy the given range blank area horizontally to the right of the selected range

- Move this new data further to the right of the copied range by the same number of cells.

- For example if you copy the range 5 cells to the right then move it further 5 cells to the right.

- Copy it back the same number of cells (copy it back 5 cells)

This range will contain the same formulas as in the given range.

ssaqibh:

Thanks so much for your follow-up answer ... I really appreciate it, even after I had marked  one of the answers for the solution already.

That's what I enjoy so much about EE -- everyone offers so much help, and so thoroughly!

Have a great week, ssaqibh.

Tim.