Copying a Ranges Values Only

[TheRobster]

Hi,

I am trying to write a VBA subroutine that is activated by a button and that copies the contents of one range to another.

For the sake of argument lets say I am trying to copy data from a range called Source_Range to a range called Destination_Range.

The problem I am having is that the source range contains formulas in each cell, and I only want to copy the results (numbers) of those formulas to the destination range. But if I use the following code:

Range("Source_Range").Copy Range("Destination_Range")

...it just copies the formulas as well.

If I use this code:

Range("Source_Range").Copy
        Range("Destinaton_Range").Select
            Selection.PasteSpecial Paste:=xlPasteValues

...it works, but it also has to select the destination range in order for it to work.

What I really want is a way to PasteSpecial the values only but without having to actually select any of the ranges, as in the first set of code (only this also copies the formulas, which isn't what I want).

Is there anyway to do this? Or does PasteSpecial always require that you select the range that you want to paste to? Is there an alternative to PasteSpecial that I could use?

Thanks in advance
-Rob

*Edit*

I also tried this, which I thought would work but it doesn't actually seem to do anything (it doesn't produce an error either, it just does nothing):

Range("Destination_Range") = Range("Source_Range")

Why doesn't this work? I thought it would do, since it works for individual cells, but not for named ranges?!?!