copy formula

[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("A1120").Formula = .Range("A110").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.

 

[KL]

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

 

[Guest]

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.

 

[KL]

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