Help with .RANGE(Cell syntax problem

  • Thread starter Thread starter Bob Walden
  • Start date Start date
B

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
 
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
 
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
 
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
 
It's not necessary - VBA will assign arguments by position as well as by
argname. I sometimes do it to make code more readable.
 
Back
Top