Hi,

I would like to copy some cells including their formulas as they are to
another location on the same data sheet. But whenever I try to do that,
the cell references are adapted. I tried all "Insert" variations but
none worked.

I can do this for every single cell by "opening" it, selecting its
content, and copying it. But this is very tedious when I have to do
this for many cells.

So, can you tell me whether this is an easier solution to this problem?


Peter

In original cells, put absolute references (ie $A$1 instead of A1)

HTH
--
AP

"Peter Frank" a écrit dans le message de
oups.com...
Hi,

I would like to copy some cells including their formulas as they are to
another location on the same data sheet. But whenever I try to do that,
the cell references are adapted. I tried all "Insert" variations but
none worked.

I can do this for every single cell by "opening" it, selecting its
content, and copying it. But this is very tedious when I have to do
this for many cells.

So, can you tell me whether this is an easier solution to this problem?


Peter

Hi Peter,

I can do this for every single cell by "opening" it, selecting its
content, and copying it. But this is very tedious when I have to do
this for many cells.

So, can you tell me whether this is an easier solution to this problem?


You can use this macro (forgot what the source is, credits to whomever
recognises this as his/her work):

Sub CopySelectionFormulae()

Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim intColCount As Integer
Dim intRowCount As Integer

'** Check that a range is selected
If Not TypeName(Selection) = "Range" Then End
'** check that the range has only one area
If Not Selection.Areas.Count = 1 Then
MsgBox "Multiple Selections Not Allowed", vbExclamation
End
End If

'** Assign selection to object variable
Set rngCopyFrom = Selection

'** This is required in case cancel is clicked.
'** Type 8 input box returns a range object if OK is
'** clicked or False if cancel is clicked.* I do not
'** know of a way to test for both cases without
'** using error trapping
On Error GoTo UserCancelled

'** Assign object variable to user-selected cell
Set rngCopyTo = Application.InputBox( _
prompt:="Select the UPPER LEFT CELL of the " & "range to which you
wish to paste", _
Title:="Copy Range Formulae", Type:=8).Cells(1, 1)

On Error GoTo 0

'** Loop through source range assigning any formulae found
'** to the equivalent cell of the destination range.
For intColCount = 1 To rngCopyFrom.Columns.Count
For intRowCount = 1 To rngCopyFrom.Rows.Count
If rngCopyFrom.Cells(intRowCount, intColCount).HasFormula Then
rngCopyTo.Offset(intRowCount - 1, _
intColCount - 1).Formula = _
rngCopyFrom.Cells(intRowCount, _
intColCount).Formula
End If
Next intRowCount
Next intColCount

UserCancelled:

End Sub

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

One way:
Excel recognizes that the formulas are formulas by the leading equal (=)
sign and treats them as you describe. The solution is to change that so
that Excel does not think they are formulas. Do this:
Select all the cells you want to copy.
Do Edit - Replace.
In "What to find", type "=" without the quotes.
In "Replace with" type "Peter=" without the quotes.
Copy and paste all the cells.
Select all the cells you pasted and do all that in reverse (replace Peter=
with just =).
Done. HTH Otto
"Peter Frank" wrote in message
oups.com...
Hi,

I would like to copy some cells including their formulas as they are to
another location on the same data sheet. But whenever I try to do that,
the cell references are adapted. I tried all "Insert" variations but
none worked.

I can do this for every single cell by "opening" it, selecting its
content, and copying it. But this is very tedious when I have to do
this for many cells.

So, can you tell me whether this is an easier solution to this problem?


Peter

One manual way...

Select the range to copy
edit|replace
what: = (equal sign)
with: $$$$$
replace all

Now copy and paste those values (they're no longer formulas).

Now change the values back to formulas:
select the range
edit|replace
what: $$$$$
with: =
replace all

And don't forget to fix the original copied range, too.

Peter Frank wrote:

Hi,

I would like to copy some cells including their formulas as they are to
another location on the same data sheet. But whenever I try to do that,
the cell references are adapted. I tried all "Insert" variations but
none worked.

I can do this for every single cell by "opening" it, selecting its
content, and copying it. But this is very tedious when I have to do
this for many cells.

So, can you tell me whether this is an easier solution to this problem?

Peter

--

Dave Peterson

that's a clever trick. I was experimenting with it and replace = with '=.
This turned the cells into text but I couldn't automate the change back (I
assume this is why you inserted Peter instead). Why can Excel not find the
leading apostrophe in the cells?

"Otto Moehrbach" wrote:

One way:
Excel recognizes that the formulas are formulas by the leading equal (=)
sign and treats them as you describe. The solution is to change that so
that Excel does not think they are formulas. Do this:
Select all the cells you want to copy.
Do Edit - Replace.
In "What to find", type "=" without the quotes.
In "Replace with" type "Peter=" without the quotes.
Copy and paste all the cells.
Select all the cells you pasted and do all that in reverse (replace Peter=
with just =).
Done. HTH Otto
"Peter Frank" wrote in message
oups.com...
Hi,

I would like to copy some cells including their formulas as they are to
another location on the same data sheet. But whenever I try to do that,
the cell references are adapted. I tried all "Insert" variations but
none worked.

I can do this for every single cell by "opening" it, selecting its
content, and copying it. But this is very tedious when I have to do
this for many cells.

So, can you tell me whether this is an easier solution to this problem?


Peter

Why does the formula have a leading apostrophe? Otto
"Sloth" wrote in message
...
that's a clever trick. I was experimenting with it and replace = with '=.
This turned the cells into text but I couldn't automate the change back (I
assume this is why you inserted Peter instead). Why can Excel not find
the
leading apostrophe in the cells?

"Otto Moehrbach" wrote:

One way:
Excel recognizes that the formulas are formulas by the leading equal (=)
sign and treats them as you describe. The solution is to change that so
that Excel does not think they are formulas. Do this:
Select all the cells you want to copy.
Do Edit - Replace.
In "What to find", type "=" without the quotes.
In "Replace with" type "Peter=" without the quotes.
Copy and paste all the cells.
Select all the cells you pasted and do all that in reverse (replace
Peter=
with just =).
Done. HTH Otto
"Peter Frank" wrote in message
oups.com...
Hi,

I would like to copy some cells including their formulas as they are to
another location on the same data sheet. But whenever I try to do that,
the cell references are adapted. I tried all "Insert" variations but
none worked.

I can do this for every single cell by "opening" it, selecting its
content, and copying it. But this is very tedious when I have to do
this for many cells.

So, can you tell me whether this is an easier solution to this problem?


Peter

I used the "Find and Replace" feature to insert an apostrophe before the
equal sign (instead of Peter). I was just curious why I couldn't use the
find and replace feature to remove the apostrophe. It says it can't locate
it in any of the cells.

NOTE: I understand you didn't say to put an apostrophe there. I am just
being curious.

"Otto Moehrbach" wrote:

Why does the formula have a leading apostrophe? Otto
"Sloth" wrote in message
...
that's a clever trick. I was experimenting with it and replace = with '=.
This turned the cells into text but I couldn't automate the change back (I
assume this is why you inserted Peter instead). Why can Excel not find
the
leading apostrophe in the cells?

"Otto Moehrbach" wrote:

One way:
Excel recognizes that the formulas are formulas by the leading equal (=)
sign and treats them as you describe. The solution is to change that so
that Excel does not think they are formulas. Do this:
Select all the cells you want to copy.
Do Edit - Replace.
In "What to find", type "=" without the quotes.
In "Replace with" type "Peter=" without the quotes.
Copy and paste all the cells.
Select all the cells you pasted and do all that in reverse (replace
Peter=
with just =).
Done. HTH Otto
"Peter Frank" wrote in message
oups.com...
Hi,

I would like to copy some cells including their formulas as they are to
another location on the same data sheet. But whenever I try to do that,
the cell references are adapted. I tried all "Insert" variations but
none worked.

I can do this for every single cell by "opening" it, selecting its
content, and copying it. But this is very tedious when I have to do
this for many cells.

So, can you tell me whether this is an easier solution to this problem?


Peter

Jan Karel Pieterse wrote:

Hi Peter,

I can do this for every single cell by "opening" it, selecting its
content, and copying it. But this is very tedious when I have to do
this for many cells.

So, can you tell me whether this is an easier solution to this problem?


You can use this macro (forgot what the source is, credits to whomever
recognises this as his/her work):

Sub CopySelectionFormulae()

Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim intColCount As Integer
Dim intRowCount As Integer

'** Check that a range is selected
If Not TypeName(Selection) = "Range" Then End
'** check that the range has only one area
If Not Selection.Areas.Count = 1 Then
MsgBox "Multiple Selections Not Allowed", vbExclamation
End
End If

'** Assign selection to object variable
Set rngCopyFrom = Selection

'** This is required in case cancel is clicked.
'** Type 8 input box returns a range object if OK is
'** clicked or False if cancel is clicked.* I do not
'** know of a way to test for both cases without
'** using error trapping
On Error GoTo UserCancelled

'** Assign object variable to user-selected cell
Set rngCopyTo = Application.InputBox( _
prompt:="Select the UPPER LEFT CELL of the " & "range to which you
wish to paste", _
Title:="Copy Range Formulae", Type:=8).Cells(1, 1)

On Error GoTo 0

'** Loop through source range assigning any formulae found
'** to the equivalent cell of the destination range.
For intColCount = 1 To rngCopyFrom.Columns.Count
For intRowCount = 1 To rngCopyFrom.Rows.Count
If rngCopyFrom.Cells(intRowCount, intColCount).HasFormula Then
rngCopyTo.Offset(intRowCount - 1, _
intColCount - 1).Formula = _
rngCopyFrom.Cells(intRowCount, _
intColCount).Formula
End If
Next intRowCount
Next intColCount

UserCancelled:

End Sub

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com


Hi Jan,

Thanks for the macro. Using Google Groups search I also found the
original posting of this macro. It was posted by a guy named Rob Bruce
and it appears he used to have an Excel webpage but it doesn't exist
anymore.

Anyway, the macro works pretty nicely except for one strange thing:
Whenever I select a range of cells to be copied with that macro, I
always get a copy of the cells one column to the left of the cells
that I actually selected. Knowing this I can select the cells one
column to the right of the cells that I would like to copy, but do you
have any idea what this could be due to or how to modify the macro so
that it copies the cells that I selected?

Peter

Excel sees that leading apostrophe as a prefixcharacter--it tells excel to treat
the cell as text (so you don't have to preformat the cell as text).

That was a bad choice to try <vbg.

Sloth wrote:

I used the "Find and Replace" feature to insert an apostrophe before the
equal sign (instead of Peter). I was just curious why I couldn't use the
find and replace feature to remove the apostrophe. It says it can't locate
it in any of the cells.

NOTE: I understand you didn't say to put an apostrophe there. I am just
being curious.

"Otto Moehrbach" wrote:

Why does the formula have a leading apostrophe? Otto
"Sloth" wrote in message
...
that's a clever trick. I was experimenting with it and replace = with '=.
This turned the cells into text but I couldn't automate the change back (I
assume this is why you inserted Peter instead). Why can Excel not find
the
leading apostrophe in the cells?

"Otto Moehrbach" wrote:

One way:
Excel recognizes that the formulas are formulas by the leading equal (=)
sign and treats them as you describe. The solution is to change that so
that Excel does not think they are formulas. Do this:
Select all the cells you want to copy.
Do Edit - Replace.
In "What to find", type "=" without the quotes.
In "Replace with" type "Peter=" without the quotes.
Copy and paste all the cells.
Select all the cells you pasted and do all that in reverse (replace
Peter=
with just =).
Done. HTH Otto
"Peter Frank" wrote in message
oups.com...
Hi,

I would like to copy some cells including their formulas as they are to
another location on the same data sheet. But whenever I try to do that,
the cell references are adapted. I tried all "Insert" variations but
none worked.

I can do this for every single cell by "opening" it, selecting its
content, and copying it. But this is very tedious when I have to do
this for many cells.

So, can you tell me whether this is an easier solution to this problem?


Peter








--

Dave Peterson

I wrote:

Anyway, the macro works pretty nicely except for one strange thing:
Whenever I select a range of cells to be copied with that macro, I
always get a copy of the cells one column to the left of the cells
that I actually selected. Knowing this I can select the cells one
column to the right of the cells that I would like to copy, but do you
have any idea what this could be due to or how to modify the macro so
that it copies the cells that I selected?

Another thing I noticed is that the macro only seems to copy multiple
formulas when they are listed below each other, i.e. in a column. When
I select formulas located next to each other, i.e. in a line, then
only the formula one column left to the first selected formula is
copied and inserted multiple times.

Could this have something to do with the fact that I use a German
version of Excel or are macro commands universal and
country-/language-independent?

Peter

Hi Peter,

Another thing I noticed is that the macro only seems to copy multiple
formulas when they are listed below each other, i.e. in a column. When
I select formulas located next to each other, i.e. in a line, then
only the formula one column left to the first selected formula is
copied and inserted multiple times.

Could this have something to do with the fact that I use a German
version of Excel or are macro commands universal and
country-/language-independent?

Strange, I don't recall seeing these issues at all. In principle macro's
are US English, so version should make little difference. Sometimes it
does though.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Jan Karel Pieterse wrote:

Hi Peter,

Another thing I noticed is that the macro only seems to copy multiple
formulas when they are listed below each other, i.e. in a column. When
I select formulas located next to each other, i.e. in a line, then
only the formula one column left to the first selected formula is
copied and inserted multiple times.

Could this have something to do with the fact that I use a German
version of Excel or are macro commands universal and
country-/language-independent?

Strange, I don't recall seeing these issues at all. In principle macro's
are US English, so version should make little difference. Sometimes it
does though.

Alright. I just changed the language scheme to "English (USA)" hoping
that this might make the macro work correctly but that didn't help
either.
Oddly enough, I don't get any error messages when using the macro.

Peter