Save a cell location and return to it

[Guest]

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

 

[Norman Jones]

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
'<<=============

 

[Guest]

Thanks Norman works well!

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
'<<=============

 

[Desert Piranha]

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

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
'<<=============

 

[Norman Jones]

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]

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

Hi Dave,

The instruction:


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"
message

 

[Norman Jones]

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]

Hi Norman,
Thanks for the example.

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

Dave

Hi Dave,

The instruction:


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"
message