Syntax of Range

My code is in standard module of a workbook. I want to select a range in say sheet3. If I use
Worksheets("sheet3").Range(Cells(1,1), Cells(5,5)).Select
it won'r work ( gives run time error ) unless sheet3 is activated before I execute this line of code.
What is the syntax to use Range property on a sheet which has not been activated?

A V Veerkar

Hi, you cannot SELECT a range on a sheet that is neither visible or activated, to use select you would have to select the sheet, but you do not need to select things in order to manipulate them, take a look at this:

Option Explicit

Sub copypaste_without_selecting()
Dim Rng As Range
Set Rng = Sheets("Sheet3").Range("A1:E5") '''sets range without selecting
Rng.Copy Destination:=Sheets("Sheet1").Range("A1") '''copies range without selecting
End Sub                                       '''pastes to Sheet1 without selecting

even if you were on sheet 2 and ran tis code it would perform the task!

Regards,
Simon

Thanks a lt Simon. That will help.
A V Veerkar

Originally Posted by Simon Lloyd

Hi, you cannot SELECT a range on a sheet that is neither visible or activated, to use select you would have to select the sheet, but you do not need to select things in order to manipulate them, take a look at this:

Option Explicit

Sub copypaste_without_selecting()
Dim Rng As Range
Set Rng = Sheets("Sheet3").Range("A1:E5") '''sets range without selecting
Rng.Copy Destination:=Sheets("Sheet1").Range("A1") '''copies range without selecting
End Sub                                       '''pastes to Sheet1 without selecting

even if you were on sheet 2 and ran tis code it would perform the task!

Regards,
Simon

I am on sheet1 and I run this code
set rng=sheets(2).Range("A1:E5")
it works ok. But if I change to
set rng=sheets(2).Range(cells(1,1),cells(5,5))
it gives error. I want to use the second notation. Any idea how to use the second notation with Range property to return a range from other sheet?

A V Veerkar