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

Originally Posted by 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
'<<=============

---
Regards,
Norman


"Adam" <[email protected]> wrote in message
news:[email protected]...
> 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

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

Originally Posted by 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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>

Hi Norman,
Thanks for the example.

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

Dave

Originally Posted by 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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>