selecting range from different Worksheets ERROR

[Elie Abou Haydar]

Hello

im trying to select a range of cells from Sheet3 non active sheet.
below 2 codes are giving me error ofApplication-defined or object-defined erroreven if i use range(cells( same error.

''worksheets("Sheet3").Range("c3", Range("c2").End(xlDown)).Select

Sub SelectRangeDown()
With worksheets("Sheet3").Activate
Range("c3", Range("c2").End(xlDown)).Select
End With
End Sub

Appreciate your help,

thank you
regards

 

[shg]

You can only select on the activesheet.

In this statement,

worksheets("Sheet3").Range("c3", Range("c2").End(xlDown)).Select

worksheets("Sheet3").Range("c3" refers to a cell on Sheet3, but

Range("c2").End(xlDown) refers to a cell on the active worksheet, because Range is not qualified, and unqualified references refer to the active sheet.

That said, you almost never need to select a range to operate on it, and it slows your code.

 

[Elie Abou Haydar]

Thank you shg. now i got the concept.
but how can i use "with, end with"in this case ?or i have to refer to the worksheet name also ?

thank you
regards

 

[Norie]

What are you going to do with the range on the other sheet?

 

[Elie Abou Haydar]

actually i want to copy and past several ranges from Sheet2 to Sheet1
is there a faster way then the code below? (note: rangeA1 and A2 are just examples)

Worksheets("Sheet2").range("A1").copy Worksheets("Sheet1").range("A1")
Worksheets("Sheet2").range("A2").copy Worksheets("Sheet1").range("A2")
Worksheets("Sheet2").range("A3").copy Worksheets("Sheet1").range("A3")

thank you
regards

 

[bobgentry]

Worksheets("Sheet2").range = Worksheets("Sheet1").range("A1")

 

[bobgentry]

bah, stupid phone dropped the whole range statement. but you get the idea.
unless you need the formatting to come along for the ride, just using range("a1")=range("a2") gets the job done

 

[shg]

unless you need the formatting to come along for the ride

Or formulas.

 

[DocAElstein]

Hi

.....and unqualified references refer to the active sheet......

I think almost.....
"

Correct. In a normal module, an unqualified Range call equates to Application.Range, not ActiveSheet.Range.

http://www.mrexcel.com/forum/excel-...-range-range-value-anomaly-2.html#post4038308

But maybe I missed something again....

Alan

 

[shg]

From Help:

Application.Range Property
Returns a Range object that represents a cell or a range of cells.

[...]

Remarks
When used without an object qualifier, this property is a shortcut for ActiveSheet.Range (it returns a range from the active sheet; if the active sheet isn’t a worksheet, the property fails).

In a worksheet module, it refers to an range on that worksheet.