Copy and Paste exact formula
 

I'm trying to write a 2 macros. The 1st to copy the exact formulas of
a range and the 2nd to paste the exact formulas from the copied range
into a selected range. For example, lets say cells A1, B1, and C1 all
contain formulas and I would like to copy those exact formulas into
A2, B2, and C2. I would like this macro to also work when the formula
(s) contain references to external workbooks. C

can anyone help me solve this?

thanks!

J

Copy and Paste exact formula
 

This is kind of a dumb example. It assumes that you select cells and run
copyit and select the destination and run pasteit

The two selections must have the same number of cells.

Dim stuff(1000) As Variant

Sub copyit()
i = 0
For Each r In Selection
stuff(i) = r.Formula
i = i + 1
Next
End Sub

Sub pasteit()
i = 0
For Each r In Selection
r.Formula = stuff(i)
i = i + 1
Next
End Sub

--
Gary''s Student - gsnu2007K


"Joshua" wrote:

I'm trying to write a 2 macros. The 1st to copy the exact formulas of
a range and the 2nd to paste the exact formulas from the copied range
into a selected range. For example, lets say cells A1, B1, and C1 all
contain formulas and I would like to copy those exact formulas into
A2, B2, and C2. I would like this macro to also work when the formula
(s) contain references to external workbooks. C

can anyone help me solve this?

thanks!

J

Copy and Paste exact formula
 

Do you absolutely have to have 2 macros to do this or would a single macro
be okay?

Sub DuplicateFormulasExactly()
Range("A2:C2").Formula = Range("A1:C1).Formula
End Sub

--
Rick (MVP - Excel)


"Joshua" wrote in message
...
I'm trying to write a 2 macros. The 1st to copy the exact formulas of
a range and the 2nd to paste the exact formulas from the copied range
into a selected range. For example, lets say cells A1, B1, and C1 all
contain formulas and I would like to copy those exact formulas into
A2, B2, and C2. I would like this macro to also work when the formula
(s) contain references to external workbooks. C

can anyone help me solve this?

thanks!

J

Copy and Paste exact formula
 

On Jan 14, 2:15*pm, "Rick Rothstein"
wrote:
Do you absolutely have to have 2 macros to do this or would a single macro
be okay?

Sub DuplicateFormulasExactly()
* Range("A2:C2").Formula = Range("A1:C1).Formula
End Sub

--
Rick (MVP - Excel)

"Joshua" wrote in message

...

I'm trying to write a 2 macros. *The 1st to copy the exact formulas of
a range and the 2nd to paste the exact formulas from the copied range
into a selected range. *For example, lets say cells A1, B1, and C1 all
contain formulas and I would like to copy those exact formulas into
A2, B2, and C2. *I would like this macro to also work when the formula
(s) contain references to external workbooks. *C

can anyone help me solve this?

thanks!

J

Gary's Student - when i run your copyit macro I get a compile error on
the line stuff(i) = r.Formula which says "compile error sub or
function not defined". Could you please help me fix this? Thanks!

Yes, this needs to be 2 seperate macros b/c the purpose of it is to
select and copy the formulas of cells in the first selected range and
paste those exact formulas in a different selected range of the same
number of cells. I would like this to work for any range selected for
copy and any range selected for paste so the second solution doesnt
work for me.

Copy and Paste exact formula
 

Give this single macro solution a try. Select the range of formulas you want
to copy, run the macro and, in response to the InputBox question, either
type in or select with your mouse a single cell which represents the
top/left corner of the range you wish to copy to.

Sub DuplicateFormulasExactly()
Dim S As Range
Dim CopyAddr As Range
Set S = Selection
Set CopyAddr = Application.InputBox( _
"Click on the cell to begin copying at", _
"Input 'Copy To' Cell", Type:=8)
CopyAddr.Resize(S.Rows.Count, S.Columns.Count).Formula = S.Formula
End Sub

--
Rick (MVP - Excel)


"Joshua" wrote in message
...
On Jan 14, 2:15 pm, "Rick Rothstein"
wrote:
Do you absolutely have to have 2 macros to do this or would a single macro
be okay?

Sub DuplicateFormulasExactly()
Range("A2:C2").Formula = Range("A1:C1).Formula
End Sub

--
Rick (MVP - Excel)

"Joshua" wrote in message

...

I'm trying to write a 2 macros. The 1st to copy the exact formulas of
a range and the 2nd to paste the exact formulas from the copied range
into a selected range. For example, lets say cells A1, B1, and C1 all
contain formulas and I would like to copy those exact formulas into
A2, B2, and C2. I would like this macro to also work when the formula
(s) contain references to external workbooks. C

can anyone help me solve this?

thanks!

J

Gary's Student - when i run your copyit macro I get a compile error on
the line stuff(i) = r.Formula which says "compile error sub or
function not defined". Could you please help me fix this? Thanks!

Yes, this needs to be 2 seperate macros b/c the purpose of it is to
select and copy the formulas of cells in the first selected range and
paste those exact formulas in a different selected range of the same
number of cells. I would like this to work for any range selected for
copy and any range selected for paste so the second solution doesnt
work for me.

Copy and Paste exact formula
 

On Jan 21, 3:12*pm, "Rick Rothstein"
wrote:
Give this single macro solution a try. Select the range of formulas you want
to copy, run the macro and, in response to the InputBox question, either
type in or select with your mouse a single cell which represents the
top/left corner of the range you wish to copy to.

Sub DuplicateFormulasExactly()
* Dim S As Range
* Dim CopyAddr As Range
* Set S = Selection
* Set CopyAddr = Application.InputBox( _
* * * * * * * * * * * * * * *"Click on the cell to begin copying at", _
* * * * * * * * * * * * * * *"Input 'Copy To' Cell", Type:=8)
* CopyAddr.Resize(S.Rows.Count, S.Columns.Count).Formula = S.Formula
End Sub

--
Rick (MVP - Excel)

"Joshua" wrote in message

...
On Jan 14, 2:15 pm, "Rick Rothstein"



wrote:
Do you absolutely have to have 2 macros to do this or would a single macro
be okay?

Sub DuplicateFormulasExactly()
Range("A2:C2").Formula = Range("A1:C1).Formula
End Sub

--
Rick (MVP - Excel)

"Joshua" wrote in message

....

I'm trying to write a 2 macros. The 1st to copy the exact formulas of
a range and the 2nd to paste the exact formulas from the copied range
into a selected range. For example, lets say cells A1, B1, and C1 all
contain formulas and I would like to copy those exact formulas into
A2, B2, and C2. I would like this macro to also work when the formula
(s) contain references to external workbooks. C

can anyone help me solve this?

thanks!

J

Gary's Student - when i run your copyit macro I get a compile error on
the line stuff(i) = r.Formula which says "compile error sub or
function not defined". *Could you please help me fix this? *Thanks!

Yes, this needs to be 2 seperate macros b/c the purpose of it is to
select and copy the formulas of cells in the first selected range and
paste those exact formulas in a different selected range of the same
number of cells. *I would like this to work for any range selected for
copy and any range selected for paste so the second solution doesnt
work for me.

Yes! Thank you so much! You are the man.

J