Save a cell location and return to it
 

How do I save a cells location in VB so I can chnge the active cell to
another sheet and then return later on the the saved cells location.

Thanks

Adam

Save a cell location and return to it
 

Hi Adam,

Try something like:

'=============
Public Sub Tester001()
Dim rng As Range

Set rng = ActiveCell

'your code, e.g.:
ActiveSheet.Next.Select

Application.Goto rng

End Sub
'<<=============


---
Regards,
Norman


"Adam" wrote in message
...
How do I save a cells location in VB so I can chnge the active cell to
another sheet and then return later on the the saved cells location.

Thanks

Adam

Save a cell location and return to it
 

Thanks Norman works well!

"Norman Jones" wrote:

Hi Adam,

Try something like:

'=============
Public Sub Tester001()
Dim rng As Range

Set rng = ActiveCell

'your code, e.g.:
ActiveSheet.Next.Select

Application.Goto rng

End Sub
'<<=============


---
Regards,
Norman


"Adam" wrote in message
...
How do I save a cells location in VB so I can chnge the active cell to
another sheet and then return later on the the saved cells location.

Thanks

Adam

Save a cell location and return to it
 

Hi Norman,
Would you dissect this line for me.

ActiveSheet.Next.Select

Is it saying on the next active sheet select?
Application.Goto rng

That line don't work for me. 'ActiveSheet.Next.Select'
I get:
'Object Variable or With Block Variable not set'
But if i comment that line out it works.
Thx
Dave

Norman Jones Wrote:
Hi Adam,
Try something like:
'=============
Public Sub Tester001()
Dim rng As Range
Set rng = ActiveCell
'your code, e.g.:
ActiveSheet.Next.Select
Application.Goto rng
End Sub
'<<=============

---
Regards,
Norman


"Adam" wrote in message
...
How do I save a cells location in VB so I can chnge the active cell
to
another sheet and then return later on the the saved cells location.

Thanks

Adam


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=498658

Save a cell location and return to it
 

Hi Dave,

The instruction:

ActiveSheet.Next.Select

selects (as you might intuitively anticipate) the next sheet.

If, as I suspect in your case, the active sheet is the last sheet, then the
expression will generate your encountered error.

Of course, in real code, provision would be made to handle this eventuality
but, here, the instruction was merely used to to effect an intentional
change of selection and any other selection instruction might equally well
have been used. The intention was merely to demonstrate the returm to a
given location after an intervening selection.

---
Regards,
Norman



"Desert Piranha"
<Desert.Piranha.21rnca_1137479700.9615@excelforu m-nospam.com wrote in
message news:Desert.Piranha.21rnca_1137479700.9615@excelfo rum-nospam.com...

Hi Norman,
Would you dissect this line for me.

ActiveSheet.Next.Select

Is it saying on the next active sheet select?
Application.Goto rng

That line don't work for me. 'ActiveSheet.Next.Select'
I get:
'Object Variable or With Block Variable not set'
But if i comment that line out it works.
Thx
Dave

Norman Jones Wrote:
Hi Adam,
Try something like:
'=============
Public Sub Tester001()
Dim rng As Range
Set rng = ActiveCell
'your code, e.g.:
ActiveSheet.Next.Select
Application.Goto rng
End Sub
'<<=============

---
Regards,
Norman


"Adam" wrote in message
...
How do I save a cells location in VB so I can chnge the active cell
to
another sheet and then return later on the the saved cells location.

Thanks

Adam


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=498658

Save a cell location and return to it
 

Hi Norman,

Thanks for the in depth explanation. I understand it now.
You are correct in your suspicion, that the active sheet before that
line, was the last one.

Thanks again
Dave
Norman Jones Wrote:
Hi Dave,

The instruction:

ActiveSheet.Next.Select

selects (as you might intuitively anticipate) the next sheet.

If, as I suspect in your case, the active sheet is the last sheet, then
the
expression will generate your encountered error.

Of course, in real code, provision would be made to handle this
eventuality
but, here, the instruction was merely used to to effect an intentional
change of selection and any other selection instruction might equally
well
have been used. The intention was merely to demonstrate the returm to
a
given location after an intervening selection.

---
Regards,
Norman



"Desert Piranha"
<Desert.Piranha.21rnca_1137479700.9615@excelforu m-nospam.com wrote in
message
news:Desert.Piranha.21rnca_1137479700.9615@excelfo rum-nospam.com...

Hi Norman,
Would you dissect this line for me.

ActiveSheet.Next.Select

Is it saying on the next active sheet select?
Application.Goto rng

That line don't work for me. 'ActiveSheet.Next.Select'
I get:
'Object Variable or With Block Variable not set'
But if i comment that line out it works.
Thx
Dave

Norman Jones Wrote:
Hi Adam,
Try something like:
'=============
Public Sub Tester001()
Dim rng As Range
Set rng = ActiveCell
'your code, e.g.:
ActiveSheet.Next.Select
Application.Goto rng
End Sub
'<<=============

---
Regards,
Norman


"Adam" wrote in message
...
How do I save a cells location in VB so I can chnge the active
cell
to
another sheet and then return later on the the saved cells
location.

Thanks

Adam


--
Desert Piranha



------------------------------------------------------------------------
Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
View this thread:
http://www.excelforum.com/showthread...hreadid=498658



--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=498658

Save a cell location and return to it
 

Hi Dave,

The instruction:

ActiveSheet.Next.Select

selects (as you might intuitively anticipate) the next sheet.

If, as I suspect in your case, the active sheet is the last sheet, then the
expression will generate your encountered error.

Of course, in real code, provision would be made to handle this eventuality
but, here, the instruction was merely used to to effect an intentional
change of selection and any other selection instruction might equally well
have been used. The intention was merely to demonstrate the return to a
given location after an intervening selection.

The following example uses ActiveSheet.Next to cycle through the sheets of
the active workbbook and restart at the first sheet when the last sheet is
reached:

'=============
Sub CycleSheets()
With ActiveSheet
IIf(.Index < Sheets.Count, .Next, Sheets(1)).Select
End With
End Sub
'<<=============


---
Regards,
Norman



"Desert Piranha"
<Desert.Piranha.21rnca_1137479700.9615@excelforu m-nospam.com wrote in
message news:Desert.Piranha.21rnca_1137479700.9615@excelfo rum-nospam.com...

Hi Norman,
Would you dissect this line for me.

ActiveSheet.Next.Select

Is it saying on the next active sheet select?
Application.Goto rng

That line don't work for me. 'ActiveSheet.Next.Select'
I get:
'Object Variable or With Block Variable not set'
But if i comment that line out it works.
Thx
Dave

Norman Jones Wrote:
Hi Adam,
Try something like:
'=============
Public Sub Tester001()
Dim rng As Range
Set rng = ActiveCell
'your code, e.g.:
ActiveSheet.Next.Select
Application.Goto rng
End Sub
'<<=============

---
Regards,
Norman


"Adam" wrote in message
...
How do I save a cells location in VB so I can chnge the active cell
to
another sheet and then return later on the the saved cells location.

Thanks

Adam


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=498658

Save a cell location and return to it
 

Hi Norman,
Thanks for the example.

Adam Didn't mean to hijack your thread, but i think you solved you
query.

Dave
Norman Jones Wrote:
Hi Dave,

The instruction:

ActiveSheet.Next.Select

selects (as you might intuitively anticipate) the next sheet.

If, as I suspect in your case, the active sheet is the last sheet, then
the
expression will generate your encountered error.

Of course, in real code, provision would be made to handle this
eventuality
but, here, the instruction was merely used to to effect an intentional
change of selection and any other selection instruction might equally
well
have been used. The intention was merely to demonstrate the return to
a
given location after an intervening selection.

The following example uses ActiveSheet.Next to cycle through the
sheets of
the active workbbook and restart at the first sheet when the last sheet
is
reached:

'=============
Sub CycleSheets()
With ActiveSheet
IIf(.Index < Sheets.Count, .Next, Sheets(1)).Select
End With
End Sub
'<<=============


---
Regards,
Norman



"Desert Piranha"
<Desert.Piranha.21rnca_1137479700.9615@excelforu m-nospam.com wrote in
message
news:Desert.Piranha.21rnca_1137479700.9615@excelfo rum-nospam.com...

Hi Norman,
Would you dissect this line for me.

ActiveSheet.Next.Select

Is it saying on the next active sheet select?
Application.Goto rng

That line don't work for me. 'ActiveSheet.Next.Select'
I get:
'Object Variable or With Block Variable not set'
But if i comment that line out it works.
Thx
Dave

Norman Jones Wrote:
Hi Adam,
Try something like:
'=============
Public Sub Tester001()
Dim rng As Range
Set rng = ActiveCell
'your code, e.g.:
ActiveSheet.Next.Select
Application.Goto rng
End Sub
'<<=============

---
Regards,
Norman


"Adam" wrote in message
...
How do I save a cells location in VB so I can chnge the active
cell
to
another sheet and then return later on the the saved cells
location.

Thanks

Adam


--
Desert Piranha



------------------------------------------------------------------------
Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
View this thread:
http://www.excelforum.com/showthread...hreadid=498658



--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=498658