Help with .RANGE(Cell syntax problem

[Bob Walden]

Can someone steer me in the right direction on the following problem?

I'm copying a partial range of column data from one worksheet to
another. I need to be able to use variables to control the range
copied.

This sample works ok....
Worksheets("Original").Range("B7:B10").Copy _
Worksheets("Inventory").Range("a2:a5")

But this does not...
Worksheets("Original").Range(Cells(7, 2), Cells(10,2)).Copy _
Worksheets("Inventory").Range("a2:a5")

I'll eventually need to use the second syntax to control the range
selection similar to this:
Worksheets("Original").Range(Cells(r, 2), Cells(r+3,2)).Copy .......

Am I missing something? The VBA help file leads me to believe that
I'm using the proper form. Would appreciate any help.

Thanks in advance!

Bob

 

[JE McGimpsey]

This bites just about everyone at one time or another...

Cells defaults to the Activesheet when not qualified. Therefore your

Worksheets("Original").Range(Cells(7, 2), Cells(10,2)).Copy _
Worksheets("Inventory").Range("a2:a5")

is equivalent to

Worksheets("Original").Range(ActiveSheet.Cells(7, 2), _
ActiveSheet.Cells(10,2)).Copy Destination:= _
Worksheets("Inventory").Range("a2:a5")

Instead, use the With...End with syntax to fully qualify Cells:

With Worksheets("Original")
.Range(.Cells(7, 2), .Cells(10, 2)).Copy _
Destination:=Worksheets("Inventory").Range("A2:A5")
End With

 

[keepITcool]

ws.Cells(r,2).Resize(3,1).copy ?

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Alan Beban]

You might want to consider

Set rng = Worksheets("Original").Range("A1")
Range(rng(7, 2), rng(10,2)).Copy Worksheets("Inventory").Range("a2:a5")

Alan Beban

 

[Bob Walden]

Thanks, JE

That solved the problem. Thanks!
I'm very surprised that the VBA's help & syntax examples don't clarify
this basic use of the Range function. It would have been nice to know
that the CELL range defaults to the Active worksheet (I would assume
that specifying the worksheet name in the initial argument would be
sufficient logic). Sigh... such are the challenges of using a
semi-structured variants in a shoot-from-the-hip programming language.

This is how I finally solved the problem:

Set dest = Sheets("New")
Set src = Sheets("Original")
first=7
last=922
src.Range(src.Cells(first, 2), src.Cells(last, 2)).Copy _
Destination:=dest.Range("A2")

Another mistake on my part was leaving out the "Destination:="
qualifier

Thanks again for your help,
Bob

 

[JE McGimpsey]

It's not necessary - VBA will assign arguments by position as well as by
argname. I sometimes do it to make code more readable.