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 EditReplace) 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


"dave k" wrote in message
...
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.

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" wrote:

Hi Dave,

One way:

Select your cells with formulas to copy
Press Ctrl+H (or go to menu EditReplace) 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


"dave k" wrote in message
...
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,

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


"dave k" wrote in message
...
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" wrote:

Hi Dave,

One way:

Select your cells with formulas to copy
Press Ctrl+H (or go to menu EditReplace) 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


"dave k" wrote in message
...
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.