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

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
>

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" <(E-Mail Removed)> wrote in message
news:d1744c38-52f6-4f65-b09a-(E-Mail Removed)...
> 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

On Jan 14, 2:15*pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> 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" <joshua.moa...@gmail.com> wrote in message
>
> news:d1744c38-52f6-4f65-b09a-(E-Mail Removed)...
>
> > 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.

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" <(E-Mail Removed)> wrote in message
news:180dfb86-e894-45be-b045-(E-Mail Removed)...
On Jan 14, 2:15 pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> 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" <joshua.moa...@gmail.com> wrote in message
>
> news:d1744c38-52f6-4f65-b09a-(E-Mail Removed)...
>
> > 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.

On Jan 21, 3:12*pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> 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" <joshua.moa...@gmail.com> wrote in message
>
> news:180dfb86-e894-45be-b045-(E-Mail Removed)...
> On Jan 14, 2:15 pm, "Rick Rothstein"
>
>
>
> <rick.newsNO.S...@NO.SPAMverizon.net> 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" <joshua.moa...@gmail.com> wrote in message
>
> >news:d1744c38-52f6-4f65-b09a-(E-Mail Removed)....
>
> > > 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