Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about how to use range in VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about how to use range in VBA
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 On 22 Jan., 01:24, Andrew wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about how to use range in VBA
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. Andrew wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range question | Excel Programming | |||
range name question | Excel Programming | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
range: question | Excel Programming | |||
Range.Formula and Range question using Excel Automation | Excel Programming |