Question about how to use range in VBA

[Andrew]

Hello,
I am trying to cut and paste one range from one sheet to the same
range in a different sheet. I used the following code:


Range(Cells(startrow, 1), Cells(endrow, 12)).Select
Selection.Copy

Worksheets("SUMM").Activate
Range(Cells(startrow, 1), Cells(endrow, 12)).Select --- ERROR HERE
ActiveSheet.Paste

This code gives me an error where indicated: Application defined or
Object defined error

Notice that I use the exact same line of code three lines earlier and
I get no error. If I change my code to read as

Range(Cells(startrow, 1), Cells(endrow, 12)).Select
Selection.Copy

Worksheets("SUMM").Activate
Range("A12").Select --- NO ERROR
ActiveSheet.Paste

I get no error. What am I doing wrong?

Also, one other question. When I write Range(Cells(startrow, 1), Cells
(endrow, 12)).Select
I get a bunch of highlighted cells. How do I unselect them in VBA?

thanks

 

[Per Jessen]

Hi

Not sure why the code gives you an error. Which kind of error do you
get.

Your code can be reduced to this single statement, and then you have
no highlighted cells:

Range(Cells(startrow, 1), Cells(endrow, 12)).Copy Worksheets
("SUMM").Cells(startrow, 1)

Hopes this helps.
....
Per

 

[Dave Peterson]

If your code is in a general module, then the unqualifed ranges refer to the
activesheet.

But I'm guessing that the code is behind a worksheet. That means the
unqualified ranges belong to the sheet that owns the code.

And since you've selected a different sheet, you're trying to select a range on
a non-active sheet.

worksheets("summ").activate
worksheets("Summ").range(worksheets("Summ").cells(startrow, 1), _
worksheets("Summ").cells(endrow,12)).select

will work.

Or

with worksheets("summ")
.activate
.range(.cells(startrow,1), .cells(endrow,12)).select
end with

if you want to save typing.

Remember that you don't need to select an object/range to work with it.