Hello Excel Users-

I am working on a giant monster with 18 tabs on it. I want one of my cells
on one of my tabs to just take me directly to another tab. The point of this
is to stop duplicating information; I don't want to cram the same information
into two places when it just doesn't need to be done. I would rather just
have a special cell take you directly to another tab when you click on that
cell.

I know how to insert formulas and values from Tab A into a cell in Tab B,
but I am struggling to get the entirely of Tab A into Cell 1 in Tab B.

Am I creating a link here? Do I need to insert another workbook somehow? Is
this possible? Does it make sense? Can you help me?

Thank you,

Melanie

Melanie -
it sounds like you are asking 2 different things. if you want to
physically travel from one sheet to another with the click (or maybe
double-click?) of a certain cell, i can do that.
but then you talk about getting all the information in tab A into
sheet B's first cell. that's a little more difficult!

so i'm going to address the movement one, first. pick a cell, i've
picked A1 just to be easy, in each sheet. leave that cell blank in
all worksheets

i'm not a guru. i've completed this lovely little macro, which works
wonderfully, in whatever worksheet module you put it in. but the only
way i can think of getting it to work for you is for you to enter it
18 times(!!!) in each spreadsheet module!

i will post the macro just in case nobody else responds. sorry.
'========Start of Macro==============
Option Explicit

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Dim wb As Workbook
Dim x As Long
Dim i As Long
Dim Wks As Worksheet
Dim r As Range

If Target.Address = "$A$1" Then

Set wb = ActiveWorkbook

x = wb.Sheets.Count

Set Wks = ActiveSheet
i = Wks.Index
If i = x Then
i = 1
Else
i = i + 1
End If
Set Wks = wb.Worksheets(i)
Wks.Activate
End If

End Sub
'===========End of Macro==============
hope it helps.
susan



On Oct 20, 1:36*pm, Melanie G. <Melan...@discussions.microsoft.com>
wrote:
> Hello Excel Users-
>
> I am working on a giant monster with 18 tabs on it. I want one of my cells
> on one of my tabs to just take me directly to another tab. The point of this
> is to stop duplicating information; I don't want to cram the same information
> into two places when it just doesn't need to be done. I would rather just
> have a special cell take you directly to another tab when you click on that
> cell.
>
> I know how to insert formulas and values from Tab A into a cell in Tab B,
> but I am struggling to get the entirely of Tab A into Cell 1 in Tab B.
>
> Am I creating a link here? Do I need to insert another workbook somehow? Is
> this possible? Does it make sense? Can you help me?
>
> Thank you,
>
> Melanie

Susan

You don't have to replicate your event code 18 times.

Just place it once in Thisworkbook module as this event type.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal _
Sh As Object, ByVal Target As Range, Cancel As Boolean)


Gord Dibben MS Excel MVP

On Tue, 20 Oct 2009 11:17:02 -0700 (PDT), Susan <(E-Mail Removed)> wrote:

>Melanie -
>it sounds like you are asking 2 different things. if you want to
>physically travel from one sheet to another with the click (or maybe
>double-click?) of a certain cell, i can do that.
>but then you talk about getting all the information in tab A into
>sheet B's first cell. that's a little more difficult!
>
>so i'm going to address the movement one, first. pick a cell, i've
>picked A1 just to be easy, in each sheet. leave that cell blank in
>all worksheets
>
>i'm not a guru. i've completed this lovely little macro, which works
>wonderfully, in whatever worksheet module you put it in. but the only
>way i can think of getting it to work for you is for you to enter it
>18 times(!!!) in each spreadsheet module!
>
>i will post the macro just in case nobody else responds. sorry.
>'========Start of Macro==============
>Option Explicit
>
>Private Sub Worksheet_BeforeDoubleClick _
> (ByVal Target As Range, Cancel As Boolean)
>
>Dim wb As Workbook
>Dim x As Long
>Dim i As Long
>Dim Wks As Worksheet
>Dim r As Range
>
>If Target.Address = "$A$1" Then
>
>Set wb = ActiveWorkbook
>
>x = wb.Sheets.Count
>
>Set Wks = ActiveSheet
> i = Wks.Index
> If i = x Then
> i = 1
> Else
> i = i + 1
> End If
>Set Wks = wb.Worksheets(i)
>Wks.Activate
>End If
>
>End Sub
>'===========End of Macro==============
>hope it helps.
>susan
>
>
>
>On Oct 20, 1:36*pm, Melanie G. <Melan...@discussions.microsoft.com>
>wrote:
>> Hello Excel Users-
>>
>> I am working on a giant monster with 18 tabs on it. I want one of my cells
>> on one of my tabs to just take me directly to another tab. The point of this
>> is to stop duplicating information; I don't want to cram the same information
>> into two places when it just doesn't need to be done. I would rather just
>> have a special cell take you directly to another tab when you click on that
>> cell.
>>
>> I know how to insert formulas and values from Tab A into a cell in Tab B,
>> but I am struggling to get the entirely of Tab A into Cell 1 in Tab B.
>>
>> Am I creating a link here? Do I need to insert another workbook somehow? Is
>> this possible? Does it make sense? Can you help me?
>>
>> Thank you,
>>
>> Melanie

Gord -
thank you! i didn't know that (obviously).

susan



On Oct 20, 5:49*pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> Susan
>
> You don't have to replicate your event code 18 times.
>
> Just place it once in Thisworkbook module as this event type.
>
> Private Sub Workbook_SheetBeforeDoubleClick(ByVal _
> Sh As Object, ByVal Target As Range, Cancel As Boolean)
>
> Gord Dibben *MS Excel MVP
>
>
>
> On Tue, 20 Oct 2009 11:17:02 -0700 (PDT), Susan <bogenex...@aol.com> wrote:
> >Melanie -
> >it sounds like you are asking 2 different things. *if you want to
> >physically travel from one sheet to another with the click (or maybe
> >double-click?) of a certain cell, i can do that.
> >but then you talk about getting all the information in tab A into
> >sheet B's first cell. *that's a little more difficult!
>
> >so i'm going to address the movement one, first. *pick a cell, i've
> >picked A1 just to be easy, in each sheet. *leave that cell blank in
> >all worksheets
>
> >i'm not a guru. *i've completed this lovely little macro, which works
> >wonderfully, in whatever worksheet module you put it in. *but the only
> >way i can think of getting it to work for you is for you to enter it
> >18 times(!!!) in each spreadsheet module!
>
> >i will post the macro just in case nobody else responds. *sorry. *
> >'========Start of Macro==============
> >Option Explicit
>
> >Private Sub Worksheet_BeforeDoubleClick _
> > * * * * * *(ByVal Target As Range, Cancel As Boolean)
>
> >Dim wb As Workbook
> >Dim x As Long
> >Dim i As Long
> >Dim Wks As Worksheet
> >Dim r As Range
>
> >If Target.Address = "$A$1" Then
>
> >Set wb = ActiveWorkbook
>
> >x = wb.Sheets.Count
>
> >Set Wks = ActiveSheet
> > * i = Wks.Index
> > * * *If i = x Then
> > * * * * i = 1
> > * * *Else
> > * * * * i = i + 1
> > * * *End If
> >Set Wks = wb.Worksheets(i)
> >Wks.Activate
> >End If
>
> >End Sub
> >'===========End of Macro==============
> >hope it helps.
> >susan
>
> >On Oct 20, 1:36*pm, Melanie G. <Melan...@discussions.microsoft.com>
> >wrote:
> >> Hello Excel Users-
>
> >> I am working on a giant monster with 18 tabs on it. I want one of my cells
> >> on one of my tabs to just take me directly to another tab. The point of this
> >> is to stop duplicating information; I don't want to cram the same information
> >> into two places when it just doesn't need to be done. I would rather just
> >> have a special cell take you directly to another tab when you click onthat
> >> cell.
>
> >> I know how to insert formulas and values from Tab A into a cell in TabB,
> >> but I am struggling to get the entirely of Tab A into Cell 1 in Tab B.
>
> >> Am I creating a link here? Do I need to insert another workbook somehow? Is
> >> this possible? Does it make sense? Can you help me?
>
> >> Thank you,
>
> >> Melanie- Hide quoted text -
>
> - Show quoted text -