Copy and Paste exact formula

[Joshua]

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]

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

 

[Rick Rothstein]

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

 

[Joshua]

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

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.

 

[Rick Rothstein]

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)

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

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.

 

[Joshua]

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)





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