Set Range on worksheet

[tiredofit]

My workbook has two worksheets, Sheet1 and Sheet2.

If I put this code in a standard module, it's fine:

Sub SetRange()

    Dim rng As Range
    Set rng = Range(Sheet1.Cells(1, 1), Sheet1.Cells(100, 1))

End Sub

but why is it when I put it in Sheet2, it fails:

Private Sub Worksheet_Activate()

    Dim rng As Range
    Set rng = Range(Sheet1.Cells(1, 1), Sheet1.Cells(100,1))

End Sub

with this message:

Method 'Range' of object '_Worksheet' failed

Thanks


EDIT SORTED:

Need to fully qualify, so

Set rng = Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(100,1))

 

[mumps]

Try:

Sub SetRange()
    Dim rng As Range
    Set rng = Range(Sheets(1).Cells(1, 1), Sheets(1).Cells(100, 1))
End Sub

Private Sub Worksheet_Activate()
    Dim rng As Range
    Set rng = Sheets(1).Range(Sheets(1).Cells(1, 1), Sheets(1).Cells(100, 1))
End Sub

 

[tiredofit]

Thanks

 

[mumps]

My pleasure.

 

[Mentor82]

Hi,
It's because you refer to sheet1 within worksheet 2 module. The question is what range you want to set out of worksheet2 module. Is it a range in sheet2 or range in sheet1. Depending on your choice take below code 1 or 2 and paste in your sheet2 module.

Code to set range sheet1 out of sheet2 module:

Dim rng As Range
    Set rng = sheets(1).Range(Cells(1, 1).address, Cells(100, 1).address)

Code to set range sheet 2 out of sheet 2 module

Dim rng As Range
    Set rng = Range(Cells(1, 1),Cells(100, 1))