Using a Variable to Copy a Range

Lorgach

New Member
Joined
Jul 6, 2005
Messages
16
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,503
Members
412,671
Latest member
xcihan
Top