copy formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to create a macro that will copy a range of formulas and the paste
them into a new range keeping all cell references absolute. I can accomplish
this in vba using:

..Range("A11:D20").Formula = .Range("A1:D10").Formula

But then I need to have a screen to get the source and target ranges. Is
there a way to copy and then paste special (my vba special) absolute
references on paste command? Like Ctrl C to copy and then Ctrl Z to paste
the exact formulas instead of Ctrl V to paste relative formulas? I don't
want to add $ signs and sometimes it isn't practicle.

Thanks,
Dave


Thanks for the help.
 
Hi Dave,

One way:

Select your cells with formulas to copy
Press Ctrl+H (or go to menu Edit>Replace) and replace "=" with "'="
Copy your cells in a normal way (Ctrl+C, Ctrl+V)
Select your new cells with formulas
Press Ctrl+H and replace "'=" with "="

Regards,
KL
 
Thanks. I have seen this example. I believe you meant to write "replace '='
with '#'". I would still like to have a vba function so that I can give this
to some coworkers who will forget the steps.
Thanks again.
 
Hi Dave,

Although, it doesn't make a lot of difference, I meant what I wrote "replace
"=" with "'=" (there is an apostrophe in the latter) which is the most
common way of converting values/formulas to text.

Regards,
KL
 
Back
Top