I'm having a bizzare problem with C&P!!!!

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

Actually it's with copy and paste! If I copy a series of cells that I have
formulas in to use in another place when I paste the simple formulas are OK
but the more complex ones get trashed. It happens if I copy and paste as
you normally would and if I use special just "formulas". I've tried "all"
and the same thing happens. What am I doing wrong. What should have taken
a few hours has taken me all day to do cell by cell.

Marc

P.S. this is the reason for the rant!
 
Hi Marc,
Please post a short example of the address range you
copied from, and the formulas by address,
Next the address range you are pasting to and the formulas
you got -- and the formulas you expected.
 
I've tried it several ways. I'm creating a work book to generate quotes. I
enter everything on the first page and it fills the good, better, best
sheets which than fill three printable quotes on the same sheet. So once I
got the sheets set I figured I could just C&P the formulas and use
find/replace to make the changes. I've tried clicking the upper left square
so it high lights the entire sheet and than C&P to the other two sheets
using "paste special" - formulas and all - but got the same results as if I
just pasted regular. I've tried copying "B11:T11" and pasting to B34:T34,
"B56:T56" and "B78:T78" on one sheet and copying "AP15:25" pasting to
"AP59:69", "AP103:113 on another and copying "AB4:AR44" and pasting to
"AB48:AR88" and :AB92:AR132. The simple formulas either linking to another
cell or adding or multpling by 2 cells are OK but anything more complex gets
trashed and I used "paste special" formulas which I thought would make a
"carbon copy".

Marc
 
Hi Marc,
Repeating:
Please post a short example of the address range you
copied from, and the formulas by address.
Click to expand...

It is impossible to guess what your problem is without a
description of the problem:
what you did, what formula you copied, from where to where,
what formula go got, and what you expected.
You have to show how the formula changed and what you expected.

Even if we tried to unravel your long paragraph, there are no
formulas -- you must provide a reproducible example of your
problem.

Example: -----------
A1: 2 B1: 4 C1=8 D1=ROW() E1: =SUM(A1:D1) displays 15

copy A1:E1 paste into A2, resulted in
A2: 2 B2: 4 C2=8 D2=ROW() E1: =SUM(A2:D2) displays 16

What I expected was: (describe what you wanted/expected)
-------------------
Are you familiar with relative and absolute addresses.
Relative And Absolute Addressing
.... formula containing a relative address, Excel will adjust the row and/or column
references ... This type of cell reference is called absolute addressing. ...
http://www.cpearson.com/excel/relative.htm
 
David solved the problem and it was in the original and absolute values.
The cells that didn't paste correctly didn't have the $ making them absolute
values. How do I make sure that they have the $ when I C&P?

Marc
 
THANKS

Marc

Marc said:
David solved the problem and it was in the original and absolute values.
The cells that didn't paste correctly didn't have the $ making them absolute
values. How do I make sure that they have the $ when I C&P?

Marc


addressing.
Click to expand...
 
Marc

To change relative to absolute manually.

1. Type in the $ signs

2. F2 and highlight the address(es) in the cell and hit F4 to cycle through
choices.

OR you can run a macro to change all at once.

Here are 4 such macros......

Sub Absolute()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

Sub AbsoluteRow()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub

Sub AbsoluteCol()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub

Sub Relative()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelative)
End If
Next
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.


Gord Dibben Excel MVP
 
Back
Top