software

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 quot;Insertquot; variations but
none worked.

I can do this for every single cell by quot;openingquot; 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?PeterIn original cells, put absolute references (ie $A$1 instead of A1)

HTH
--
AP

quot;Peter Frankquot; gt; a écrit dans le message de oups.com...
gt; Hi,
gt;
gt; I would like to copy some cells including their formulas as they are to
gt; another location on the same data sheet. But whenever I try to do that,
gt; the cell references are adapted. I tried all quot;Insertquot; variations but
gt; none worked.
gt;
gt; I can do this for every single cell by quot;openingquot; it, selecting its
gt; content, and copying it. But this is very tedious when I have to do
gt; this for many cells.
gt;
gt; So, can you tell me whether this is an easier solution to this problem?
gt;
gt;
gt; Peter
gt;
Hi Peter,

gt; I can do this for every single cell by quot;openingquot; it, selecting its
gt; content, and copying it. But this is very tedious when I have to do
gt; this for many cells.
gt;
gt; So, can you tell me whether this is an easier solution to this problem?
gt;

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) = quot;Rangequot; Then End
'** check that the range has only one area
If Not Selection.Areas.Count = 1 Then
MsgBox quot;Multiple Selections Not Allowedquot;, 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:=quot;Select the UPPER LEFT CELL of the quot; amp; quot;range to which you
wish to pastequot;, _
Title:=quot;Copy Range Formulaequot;, 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
www.jkp-ads.comOne 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 quot;What to findquot;, type quot;=quot; without the quotes.
In quot;Replace withquot; type quot;Peter=quot; 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
quot;Peter Frankquot; gt; wrote in message oups.com...
gt; Hi,
gt;
gt; I would like to copy some cells including their formulas as they are to
gt; another location on the same data sheet. But whenever I try to do that,
gt; the cell references are adapted. I tried all quot;Insertquot; variations but
gt; none worked.
gt;
gt; I can do this for every single cell by quot;openingquot; it, selecting its
gt; content, and copying it. But this is very tedious when I have to do
gt; this for many cells.
gt;
gt; So, can you tell me whether this is an easier solution to this problem?
gt;
gt;
gt; Peter
gt;
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:
gt;
gt; Hi,
gt;
gt; I would like to copy some cells including their formulas as they are to
gt; another location on the same data sheet. But whenever I try to do that,
gt; the cell references are adapted. I tried all quot;Insertquot; variations but
gt; none worked.
gt;
gt; I can do this for every single cell by quot;openingquot; it, selecting its
gt; content, and copying it. But this is very tedious when I have to do
gt; this for many cells.
gt;
gt; So, can you tell me whether this is an easier solution to this problem?
gt;
gt; 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?

quot;Otto Moehrbachquot; wrote:

gt; One way:
gt; Excel recognizes that the formulas are formulas by the leading equal (=)
gt; sign and treats them as you describe. The solution is to change that so
gt; that Excel does not think they are formulas. Do this:
gt; Select all the cells you want to copy.
gt; Do Edit - Replace.
gt; In quot;What to findquot;, type quot;=quot; without the quotes.
gt; In quot;Replace withquot; type quot;Peter=quot; without the quotes.
gt; Copy and paste all the cells.
gt; Select all the cells you pasted and do all that in reverse (replace Peter=
gt; with just =).
gt; Done. HTH Otto
gt; quot;Peter Frankquot; gt; wrote in message
gt; oups.com...
gt; gt; Hi,
gt; gt;
gt; gt; I would like to copy some cells including their formulas as they are to
gt; gt; another location on the same data sheet. But whenever I try to do that,
gt; gt; the cell references are adapted. I tried all quot;Insertquot; variations but
gt; gt; none worked.
gt; gt;
gt; gt; I can do this for every single cell by quot;openingquot; it, selecting its
gt; gt; content, and copying it. But this is very tedious when I have to do
gt; gt; this for many cells.
gt; gt;
gt; gt; So, can you tell me whether this is an easier solution to this problem?
gt; gt;
gt; gt;
gt; gt; Peter
gt; gt;
gt;
gt;
gt;

Why does the formula have a leading apostrophe? Otto
quot;Slothquot; gt; wrote in message
...
gt; that's a clever trick. I was experimenting with it and replace = with '=.
gt; This turned the cells into text but I couldn't automate the change back (I
gt; assume this is why you inserted Peter instead). Why can Excel not find
gt; the
gt; leading apostrophe in the cells?
gt;
gt; quot;Otto Moehrbachquot; wrote:
gt;
gt;gt; One way:
gt;gt; Excel recognizes that the formulas are formulas by the leading equal (=)
gt;gt; sign and treats them as you describe. The solution is to change that so
gt;gt; that Excel does not think they are formulas. Do this:
gt;gt; Select all the cells you want to copy.
gt;gt; Do Edit - Replace.
gt;gt; In quot;What to findquot;, type quot;=quot; without the quotes.
gt;gt; In quot;Replace withquot; type quot;Peter=quot; without the quotes.
gt;gt; Copy and paste all the cells.
gt;gt; Select all the cells you pasted and do all that in reverse (replace
gt;gt; Peter=
gt;gt; with just =).
gt;gt; Done. HTH Otto
gt;gt; quot;Peter Frankquot; gt; wrote in message
gt;gt; oups.com...
gt;gt; gt; Hi,
gt;gt; gt;
gt;gt; gt; I would like to copy some cells including their formulas as they are to
gt;gt; gt; another location on the same data sheet. But whenever I try to do that,
gt;gt; gt; the cell references are adapted. I tried all quot;Insertquot; variations but
gt;gt; gt; none worked.
gt;gt; gt;
gt;gt; gt; I can do this for every single cell by quot;openingquot; it, selecting its
gt;gt; gt; content, and copying it. But this is very tedious when I have to do
gt;gt; gt; this for many cells.
gt;gt; gt;
gt;gt; gt; So, can you tell me whether this is an easier solution to this problem?
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; Peter
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
I used the quot;Find and Replacequot; 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.

quot;Otto Moehrbachquot; wrote:

gt; Why does the formula have a leading apostrophe? Otto
gt; quot;Slothquot; gt; wrote in message
gt; ...
gt; gt; that's a clever trick. I was experimenting with it and replace = with '=.
gt; gt; This turned the cells into text but I couldn't automate the change back (I
gt; gt; assume this is why you inserted Peter instead). Why can Excel not find
gt; gt; the
gt; gt; leading apostrophe in the cells?
gt; gt;
gt; gt; quot;Otto Moehrbachquot; wrote:
gt; gt;
gt; gt;gt; One way:
gt; gt;gt; Excel recognizes that the formulas are formulas by the leading equal (=)
gt; gt;gt; sign and treats them as you describe. The solution is to change that so
gt; gt;gt; that Excel does not think they are formulas. Do this:
gt; gt;gt; Select all the cells you want to copy.
gt; gt;gt; Do Edit - Replace.
gt; gt;gt; In quot;What to findquot;, type quot;=quot; without the quotes.
gt; gt;gt; In quot;Replace withquot; type quot;Peter=quot; without the quotes.
gt; gt;gt; Copy and paste all the cells.
gt; gt;gt; Select all the cells you pasted and do all that in reverse (replace
gt; gt;gt; Peter=
gt; gt;gt; with just =).
gt; gt;gt; Done. HTH Otto
gt; gt;gt; quot;Peter Frankquot; gt; wrote in message
gt; gt;gt; oups.com...
gt; gt;gt; gt; Hi,
gt; gt;gt; gt;
gt; gt;gt; gt; I would like to copy some cells including their formulas as they are to
gt; gt;gt; gt; another location on the same data sheet. But whenever I try to do that,
gt; gt;gt; gt; the cell references are adapted. I tried all quot;Insertquot; variations but
gt; gt;gt; gt; none worked.
gt; gt;gt; gt;
gt; gt;gt; gt; I can do this for every single cell by quot;openingquot; it, selecting its
gt; gt;gt; gt; content, and copying it. But this is very tedious when I have to do
gt; gt;gt; gt; this for many cells.
gt; gt;gt; gt;
gt; gt;gt; gt; So, can you tell me whether this is an easier solution to this problem?
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt; gt; Peter
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

Jan Karel Pieterse wrote:

gt;Hi Peter,
gt;
gt;gt; I can do this for every single cell by quot;openingquot; it, selecting its
gt;gt; content, and copying it. But this is very tedious when I have to do
gt;gt; this for many cells.
gt;gt;
gt;gt; So, can you tell me whether this is an easier solution to this problem?
gt;gt;
gt;
gt;You can use this macro (forgot what the source is, credits to whomever
gt;recognises this as his/her work):
gt;
gt;Sub CopySelectionFormulae()
gt;
gt;Dim rngCopyFrom As Range
gt;Dim rngCopyTo As Range
gt;Dim intColCount As Integer
gt;Dim intRowCount As Integer
gt;
gt;'** Check that a range is selected
gt;If Not TypeName(Selection) = quot;Rangequot; Then End
gt;'** check that the range has only one area
gt;If Not Selection.Areas.Count = 1 Then
gt; MsgBox quot;Multiple Selections Not Allowedquot;, vbExclamation
gt; End
gt;End If
gt;
gt;'** Assign selection to object variable
gt;Set rngCopyFrom = Selection
gt;
gt;'** This is required in case cancel is clicked.
gt;'** Type 8 input box returns a range object if OK is
gt;'** clicked or False if cancel is clicked.* I do not
gt;'** know of a way to test for both cases without
gt;'** using error trapping
gt;On Error GoTo UserCancelled
gt;
gt;'** Assign object variable to user-selected cell
gt;Set rngCopyTo = Application.InputBox( _
gt; prompt:=quot;Select the UPPER LEFT CELL of the quot; amp; quot;range to which you
gt;wish to pastequot;, _
gt; Title:=quot;Copy Range Formulaequot;, Type:=8).Cells(1, 1)
gt;
gt;On Error GoTo 0
gt;
gt;'** Loop through source range assigning any formulae found
gt;'** to the equivalent cell of the destination range.
gt;For intColCount = 1 To rngCopyFrom.Columns.Count
gt; For intRowCount = 1 To rngCopyFrom.Rows.Count
gt; If rngCopyFrom.Cells(intRowCount, intColCount).HasFormula Then
gt; rngCopyTo.Offset(intRowCount - 1, _
gt; intColCount - 1).Formula = _
gt; rngCopyFrom.Cells(intRowCount, _
gt; intColCount).Formula
gt; End If
gt; Next intRowCount
gt;Next intColCount
gt;
gt;UserCancelled:
gt;
gt;End Sub
gt;
gt;Regards,
gt;
gt;Jan Karel Pieterse
gt;Excel MVP
gt;www.jkp-ads.comHi 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 lt;vbggt;.

Sloth wrote:
gt;
gt; I used the quot;Find and Replacequot; feature to insert an apostrophe before the
gt; equal sign (instead of Peter). I was just curious why I couldn't use the
gt; find and replace feature to remove the apostrophe. It says it can't locate
gt; it in any of the cells.
gt;
gt; NOTE: I understand you didn't say to put an apostrophe there. I am just
gt; being curious.
gt;
gt; quot;Otto Moehrbachquot; wrote:
gt;
gt; gt; Why does the formula have a leading apostrophe? Otto
gt; gt; quot;Slothquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; that's a clever trick. I was experimenting with it and replace = with '=.
gt; gt; gt; This turned the cells into text but I couldn't automate the change back (I
gt; gt; gt; assume this is why you inserted Peter instead). Why can Excel not find
gt; gt; gt; the
gt; gt; gt; leading apostrophe in the cells?
gt; gt; gt;
gt; gt; gt; quot;Otto Moehrbachquot; wrote:
gt; gt; gt;
gt; gt; gt;gt; One way:
gt; gt; gt;gt; Excel recognizes that the formulas are formulas by the leading equal (=)
gt; gt; gt;gt; sign and treats them as you describe. The solution is to change that so
gt; gt; gt;gt; that Excel does not think they are formulas. Do this:
gt; gt; gt;gt; Select all the cells you want to copy.
gt; gt; gt;gt; Do Edit - Replace.
gt; gt; gt;gt; In quot;What to findquot;, type quot;=quot; without the quotes.
gt; gt; gt;gt; In quot;Replace withquot; type quot;Peter=quot; without the quotes.
gt; gt; gt;gt; Copy and paste all the cells.
gt; gt; gt;gt; Select all the cells you pasted and do all that in reverse (replace
gt; gt; gt;gt; Peter=
gt; gt; gt;gt; with just =).
gt; gt; gt;gt; Done. HTH Otto
gt; gt; gt;gt; quot;Peter Frankquot; gt; wrote in message
gt; gt; gt;gt; oups.com...
gt; gt; gt;gt; gt; Hi,
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; I would like to copy some cells including their formulas as they are to
gt; gt; gt;gt; gt; another location on the same data sheet. But whenever I try to do that,
gt; gt; gt;gt; gt; the cell references are adapted. I tried all quot;Insertquot; variations but
gt; gt; gt;gt; gt; none worked.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; I can do this for every single cell by quot;openingquot; it, selecting its
gt; gt; gt;gt; gt; content, and copying it. But this is very tedious when I have to do
gt; gt; gt;gt; gt; this for many cells.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; So, can you tell me whether this is an easier solution to this problem?
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; Peter
gt; gt; gt;gt; gt;
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt;
gt; gt;
gt; gt;

--

Dave Peterson