Indirect Referencing in a Macro

[Guest]

I am trying to copy some data from one worksheet to another so I can work
with it, but need to indirect reference the range. Cell D73 has starting Date
cell reference and C72 has ending date cell reference so would like to use
their contents for my Range reference. You can see I have tried using & to
build the reference. I know that Range(Range("D73").Value works for a single
cell but need to reference more than one cell?

After I get the Range problem worked out will work on Offseting it so it
covers the area I need. Just Stuck for now.

Worksheets("06 PLATER 1 JAN-JUNE").Range(& Range("D73").Value &":" &
Range("C72").Value).Copy _
Destination:=Worksheets("Reports").Range("$A$1")

 

[Edward Ulle]

Jerry,

Change your equation as follows:

Worksheets("06 PLATER 1 JAN-JUNE").Range(Range("D73").Address & ":"
& Range("C72").Address).Copy _
Destination:=Worksheets("Reports").Range("$A$1")

 

[Tom Ogilvy]

What you have should work if D73 contains something like F21 and C72
contains something like F35

if not, what do they contain?

 

[Guest]

Edward,

The Cells D73 and C72 have the Address I want in them so don't want their
address but want to use the Address that is stored in them. Also using Excell
97 and changed my macro to yours but still syntax error?

 

[Tom Ogilvy]

Ed,

Doesn't that just give the equivalent of

Worksheets("06 PLATER 1 JAN-JUNE").Range("D73:C72").Copy _
Destination:=Worksheets("Reports").Range("$A$1")

If that is what he wants, then no need to use anything else, but I don't
think that is it.

 

[Guest]

Edward Ulle,

Thank You did not see the second & put it in the macro and it worked.
Again Thank You

Jerry

 

[Edward Ulle]

Jerry,

I misread your post. Tom is right. My suggestion will only copy the
contents of the cells not the cells that are being referenced by them.

Ed

 

[Edward Ulle]

Jerry,

Sorry for the misfire. Using the Range Precedents property you can
access the cells that a simple formula such as "=A21" points to.

Using your example.

Dim r1 As Range
Dim r2 As Range

Set r1 = Worksheets("06 PLATER 1 JAN-JUNE").Range("D73").Precedents
Set r2 = Worksheets("06 PLATER 1 JAN-JUNE").Range("C72").Precedents

Worksheets("06 PLATER 1 JAN-JUNE").Range(r1.Address,r2.Address).Copy _
Destination:=Worksheets("Reports").Range("$A$1")