the following is a trivial example

dim sub test{}
dim dest as range
set dest=range("a1").end(xldown)
with workshees("sheet3")
msgbox dest.address
end with
with worksheets("sheet2")
msgbox dest.address
end iwth
end sub

will the first msgbox show the last cell in sheet 3 column A and second box
show the corresponding cell in sheet 2 even though neither of these sheets
are active sheets
or
am I to wrie a line activating the sheet each time and then write msgbox
line.
I hope I am clear.
thanks.

..

No. Dest is always going to point to the last used cell in column A from
whatever sheet you happen to be on when the code is executed. You would need
to reset it for each sheet. You can do it without actually activating each
sheet, just reference it as:
Set Dest = Worksheets("Sheet3").Range("A1").End(xlDown)
later you can change it to
Set Dest = Worksheets("Sheet1").Range("A1").End(xlDown)

In your code, basically the With and End With statements are unnecessary,
there is nothing between those statements that is a property or method of a
sheet.


Testing the code in a workbook would have revealed this, along with pointing
out the syntax and typos. Not meaning to be too harsh, but if you have Excel
available, questions like these are easily and quickly answered via testing,
and then if they don't work, you can move right along to the next step:
asking here WHY it doesn't work as expected <g

Sub test()
Dim dest As Range

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


"R..VENKATARAMAN" wrote:

the following is a trivial example

dim sub test{}
dim dest as range
set dest=range("a1").end(xldown)
with workshees("sheet3")
msgbox dest.address
end with
with worksheets("sheet2")
msgbox dest.address
end iwth
end sub

will the first msgbox show the last cell in sheet 3 column A and second box
show the corresponding cell in sheet 2 even though neither of these sheets
are active sheets
or
am I to wrie a line activating the sheet each time and then write msgbox
line.
I hope I am clear.
thanks.

..

Dest refers to a range on the activesheet. Your with statements actually
don't do anything (there's nothing inside the with statement that refers back
to the object declared in the line w/the With keyword).

To get the last cell on Sheet3, you'll need to use
set dest2=Worksheets("Sheet3").range("a1").end(xldown)
or
With Worksheets("Sheet3")
set dest2= .range(.Rows.Count, 1).end(xlup)
End With
depending on whether or not there are empty cells in your data.


To get the corresponding cell on sheet2, try
MsgBox Worksheets("Sheet2").Cells(Dest2.Row, Dest2.Column).Address(True,
True, xlA1, True)

Or
MsgBox Worksheets("Sheet2").Range(Dest2.Address).Address( True, True, xlA1,
True)


"R..VENKATARAMAN" wrote:

the following is a trivial example

dim sub test{}
dim dest as range
set dest=range("a1").end(xldown)
with workshees("sheet3")
msgbox dest.address
end with
with worksheets("sheet2")
msgbox dest.address
end iwth
end sub

will the first msgbox show the last cell in sheet 3 column A and second box
show the corresponding cell in sheet 2 even though neither of these sheets
are active sheets
or
am I to wrie a line activating the sheet each time and then write msgbox
line.
I hope I am clear.
thanks.

..

Scriveva R..VENKATARAMAN lunedì, 02/10/2006:
the following is a trivial example

dim sub test{}
dim dest as range
set dest=range("a1").end(xldown)
with workshees("sheet3")
msgbox dest.address
end with
with worksheets("sheet2")
msgbox dest.address
end iwth
end sub

will the first msgbox show the last cell in sheet 3 column A and second box
show the corresponding cell in sheet 2 even though neither of these sheets
are active sheets
or
am I to wrie a line activating the sheet each time and then write msgbox
line.


Sub test2()
Dim s As Worksheet
Dim mess As String

For Each s In Worksheets
mess = mess & _
s.Name & " " & _
s.Range("A1").End(xlDown).Address & _
Chr(10)
Next

MsgBox mess

End Sub

If you change this line:
msgbox dest.address
to
msgbox dest.address(external:=true)

You'll see the fully qualified address. It may be a nice way of debugging
problems.

"R..VENKATARAMAN" wrote:

the following is a trivial example

dim sub test{}
dim dest as range
set dest=range("a1").end(xldown)
with workshees("sheet3")
msgbox dest.address
end with
with worksheets("sheet2")
msgbox dest.address
end iwth
end sub

will the first msgbox show the last cell in sheet 3 column A and second box
show the corresponding cell in sheet 2 even though neither of these sheets
are active sheets
or
am I to wrie a line activating the sheet each time and then write msgbox
line.
I hope I am clear.
thanks.

.

--

Dave Peterson

thanks to all of you for clarifying the point.
---------------------------------------
Dave Peterson wrote:
If you change this line:
msgbox dest.address
to
msgbox dest.address(external:=true)

You'll see the fully qualified address. It may be a nice way of debugging
problems.

"R..VENKATARAMAN" wrote:

the following is a trivial example

dim sub test{}
dim dest as range
set dest=range("a1").end(xldown)
with workshees("sheet3")
msgbox dest.address
end with
with worksheets("sheet2")
msgbox dest.address
end iwth
end sub

will the first msgbox show the last cell in sheet 3 column A and second box
show the corresponding cell in sheet 2 even though neither of these sheets
are active sheets
or
am I to wrie a line activating the sheet each time and then write msgbox
line.
I hope I am clear.
thanks.

.

--

Dave Peterson