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
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 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
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" <[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,
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
Thx
Dave
"The game is afoot Watson"
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 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 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
>
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
Last edited by Desert Piranha; 01-17-2006 at 04:45 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks