Using a Variable to Copy a Range

[Lorgach]

Hi All,
Is there a way to get a Variable or Object to hold all the Data including the Formatting in a specified Range so it can be used at various stages during a Macro in order to do a similar action to Copy and Paste.

Something like:

VariableName = Range("A1")

Range("B2") = VariableName

I know that this is like copying only the Data from one Cell to the other, but the Variable can hold this Data so it can be used to input it to various Cells at various times during the execution of the Macro without having to re-select the target Cell Range each time.

I need something to hold the Data and Formatting of a large Range like Range(“A1:F15”) for use in the Macro.
At the moment I use the Copy and Paste method but this just seems very cumbersome compared to using some kind of Variable.

Thanks in advance for any suggestions.
Jim

 

[Andrew Poulsom]

A Range Object would hold the data and the formatting, but you would need to access the relevant properties to copy them to another range. You can't assign the entire object to another object. In your example it's the (default) Value property that's being assigned.

 

[oliver_penney]

how about an array? chuck it all in there and then pull it back out again?