to return range variable without activating a sheet

[venkat1926]

Plese see the following trivial macro

Public dest As Range
 Sub test()
 Set dest = Range("a1").End(xlDown)
With Worksheets("sheet3")
MsgBox dest.Address
End With
End Sub

this gives the dest.address of only the activesheet even though I have indicated it should be that of sheet3.

is it possible to get the dest. address of sheet3 without activating sheet3
if so how is the macro to be modified.? Thanks.
venkat

 

[VoG]

Try

Public dest As Range
Sub test()
Set dest = Sheets("Sheet3").Range("a1").End(xlDown)
MsgBox dest.Address
End Sub

 

[venkat1926]

OGII
sorry I did not explain correrctly
iIwant to have the code statements so tht I can change the worksheetname in the 4th line in my macro to get the dest for that sheet without activating it.
venkat

 

[jindon]

Public dest As Range
Sub test()
With Worksheets("sheet3")
   Set dest = .Range("a1").End(xlDown)
End With
MsgBox dest.Address
End Sub

Is this what you want?

 

[venkat1926]

jindon thank you

that means I have to everytime set dest . I understnad.

venkat

 

[jindon]

jindon thank you

that means I have to everytime set dest . I understnad.

venkat

I don't understand what you are after...

Because dest is a global object variable, it holds the reference until you change...

 

[venkat1926]

yes I understand.

 

[jindon]

Did you understand how to use "With" statement?

 

[venkat1926]

yes thank you