selecting range from different Worksheets ERROR

Elie Abou Haydar

New Member
Joined
Nov 18, 2015
Messages
7
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
You can only select on the activesheet.

In this statement,

Code:
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.
 
Upvote 0
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
 
Upvote 0
What are you going to do with the range on the other sheet?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top