Using a cell's contents as a reference in Macros

[JakeyC]

Hi -

I am new to both the group and any 'advanced' Excel Macros, and was
hoping someone more knowledgeable than myself could assist.

I KNOW there is a solution to the following problem but am having
trouble getting it just right.

I need to be able to copy the values in a range of cells to another
worksheet, but the destination range on that sheet is defined by a
cell, and the reference it contains will be different each time.

Bearing in mind my VBA knowledge is limited, how should I go about
doing this?

Any tips much appreciated.

[K Dales]

How are you specifying the destination in your cell - is it the address (in
text) of the destination range? If so, then you could do this (In this
example I am only pasting the values, and that the input range is A1:C5; cell
E1 contains the address to copy to; and the destination is on Sheet2):

Range("A1:C5").Copy
Sheets("Sheet2").Range(Range("E1").Value).PasteSpecial xlPasteValues

So, for example, if Range("E1").Value is "B2:D6", that is where the data
gets pasted.
HTH!

"JakeyC" wrote:

> Hi -
>
> I am new to both the group and any 'advanced' Excel Macros, and was
> hoping someone more knowledgeable than myself could assist.
>
> I KNOW there is a solution to the following problem but am having
> trouble getting it just right.
>
> I need to be able to copy the values in a range of cells to another
> worksheet, but the destination range on that sheet is defined by a
> cell, and the reference it contains will be different each time.
>
> Bearing in mind my VBA knowledge is limited, how should I go about
> doing this?
>
> Any tips much appreciated.
>
>

[Mike Fogleman]

Assign the cell value to a variable:

Dim drng As Range 'Destination
Dim srng As Range 'Source

Set drng = Sheet1.Range("B2").Value 'cell with destination reference
Set srng = Sheet1.Range("C3:P200") ' Range to copy values from
drng.Value = srng.Value

Note: this code assumes that the cell with destination reference also
includes a Sheet reference similar to 'Sheet2!D3'. If it does not, post back
with an exact exanple of what the reference value would be like.

Mike F

"JakeyC" <[email protected]> wrote in message
news:[email protected]...
> Hi -
>
> I am new to both the group and any 'advanced' Excel Macros, and was
> hoping someone more knowledgeable than myself could assist.
>
> I KNOW there is a solution to the following problem but am having
> trouble getting it just right.
>
> I need to be able to copy the values in a range of cells to another
> worksheet, but the destination range on that sheet is defined by a
> cell, and the reference it contains will be different each time.
>
> Bearing in mind my VBA knowledge is limited, how should I go about
> doing this?
>
> Any tips much appreciated.
>

[Tom Ogilvy]

Just some added info:
If E1 is on the destination sheet (as I read the problem statement) then the
modification would be:

Range("A1:C5").Copy
Sheets("Sheet2").Range(sheets("sheet2").Range("E1").Value).PasteSpecial
xlPasteValues

--
Regards,
Tom Ogilvy


"K Dales" <[email protected]> wrote in message
news:[email protected]...
> How are you specifying the destination in your cell - is it the address
(in
> text) of the destination range? If so, then you could do this (In this
> example I am only pasting the values, and that the input range is A1:C5;
cell
> E1 contains the address to copy to; and the destination is on Sheet2):
>
> Range("A1:C5").Copy
> Sheets("Sheet2").Range(Range("E1").Value).PasteSpecial xlPasteValues
>
> So, for example, if Range("E1").Value is "B2:D6", that is where the data
> gets pasted.
> HTH!
>
> "JakeyC" wrote:
>
> > Hi -
> >
> > I am new to both the group and any 'advanced' Excel Macros, and was
> > hoping someone more knowledgeable than myself could assist.
> >
> > I KNOW there is a solution to the following problem but am having
> > trouble getting it just right.
> >
> > I need to be able to copy the values in a range of cells to another
> > worksheet, but the destination range on that sheet is defined by a
> > cell, and the reference it contains will be different each time.
> >
> > Bearing in mind my VBA knowledge is limited, how should I go about
> > doing this?
> >
> > Any tips much appreciated.
> >
> >

[JakeyC]

Thanks for your help; the reference cell does not currently specify the
sheet, but can do so. In response to Tom Ogilvy's update; the reference
cell is not on the same sheet as the destination so should be
straightforward.

Currently, I get an error telling me that an object is required when I
try to run the code as it stands - do I need to add a line or
something?

Again,

Many thanks

[Tom Ogilvy]

Then K Dales has given you what you need.

Think Mike rushed his answer. I would see his as



Dim drng As Range 'Destination
Dim srng As Range 'Source

Set drng = Sheet1.Range("B2").Value 'cell with destination reference
Set srng = Sheet1.Range("C3:P200") ' Range to copy values from
Sheet2.Range(drng.Value) = srng.Value

if drng didn't contain the sheet (which you say it doesn't) and if you
wanted to change it to contain the sheet name as well as the range, you
would alter the code to:

Dim drng As Range 'Destination
Dim srng As Range 'Source

Set drng = Sheet1.Range("B2").Value 'cell with destination reference
Set srng = Sheet1.Range("C3:P200") ' Range to copy values from
Range(drng.Value) = srng.Value

but either of this will require that the range specified in B2 matches the
size and shape of srng. K Dales on the other hand, only needs to specify
the top left corner of the destination range.

--
Regards,
Tom Ogilvy




"JakeyC" <[email protected]> wrote in message
news:[email protected]...
> Thanks for your help; the reference cell does not currently specify the
> sheet, but can do so. In response to Tom Ogilvy's update; the reference
> cell is not on the same sheet as the destination so should be
> straightforward.
>
> Currently, I get an error telling me that an object is required when I
> try to run the code as it stands - do I need to add a line or
> something?
>
> Again,
>
> Many thanks
>

[JakeyC]

This has given me the solution to what would otherwise have been a
frustrating headache!

Many, many thanks.

Perhaps one day I'll be able to give others Excel advice - until then
I'll keep asking.

Cheers,

Jake